CPF5032 and SQL RPG

1930 pts.
Tags:
AS/400
AS/400 SQL
CPF5032
SQL Server
SQLRPGLE
How do I unlock the data file to avoid halt ? CL used to sort file and call an RPG that cleared a field (followed by further processing). When I switch to an SQLRPG to clear the field, I get CPF5032 in the next program. Yes, I tried the UNLOCK op code in the SQLRPG but same result. No halt using the old RPG (in 1st step), so the SQL update is definitely the cause of my hanging lock.

Software/Hardware used:
as/400

Answer Wiki

Thanks. We'll let you know when a new response is added.

You will need to step through and see where you are locking.
If you are call an RPG program with that file as an UPDATE file and you have a conditional update, You do not release the record until the next read.
So you could have it locked before you call to the SQLRPG.
Use debug an stop before the call to the SQLRPG and then look at your job locks and see if you have a record lock.
If not, the we need to see the logic of your SQLRPG program.

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • aceofdelts
    The SQL update is the 1st call in the CL jobstream. The 2nd call gets the lock halt when I use the SQL but no halt when I use the simple update RPG (instead of the SQL) as the 1st call. Thus I am sure that the 2nd call is not the problem. SQL pgm source is FVCODETL IF E Disk C/exec sql + UPDATE VCODETL SET OBBKAL = ' ' C/end-exec C/exec sql + UPDATE VCODETL SET OBTMPB = 0 C/end-exec C/exec sql + UPDATE VCODETL SET OBQFIL = 0 C/end-exec C Eval *inlr = *on C Return I did try it with the SQL "F" spec as Update and an UNLOCK op code prior to Return, but that gave the same halt
    1,930 pointsBadges:
    report
  • TomLiotta
    First, the F-spec doesn't do anything. It might even cause a problem if this is the entore program. Second, you probably should only have a single UPDATE statement. I can't tell how commitment control is set for this program, but each of the three UPDATEs will attempt to update every record in the file. If the updates from the first statement aren't committed, then the second (and third) UPDATE statements should run into trouble. They will all be trying to update everything. Try something like this:
    C/exec sql
    + UPDATE VCODETL SET ( OBBKAL, OBTMPB, OBQFIL ) = ( ‘ ‘, 0, 0 )
    C/end-exec
    
    C Eval *inlr = *on
    C Return 
    That should be the entire program based on your description. If possible, prompt the command that you use to compile the program. When you have all of the parm values set the way you want them, press <F14=Command string> to show the full formatted command string and paste it here. If you know any command parameter defaults that have been changed on your system, describe them. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Sorry... typo: "the entore program" should be "the entire program". Tom
    125,585 pointsBadges:
    report
  • CharlieBrowne
    Can you use debug and stop the program before the 2nd call when you use the SQL program. Check and see if you have record locks befoe the 2nd program is started.
    41,380 pointsBadges:
    report
  • aceofdelts
    The improved SQL went fine (thanks for the tips). The F14 shows ? CRTSQLRPGI ??OBJ(TESTMODS/COMABS) ?*SRCFILE(TESTMODS/QRPGLESRC) ?*SRCMBR(COMABS) ?*OBJTYPE(*PGM) ??REPLACE(*YES) No revised system defaults exist I put that 2nd call into debug & broke at the 1st executable statement. It seems to show that this job has a record lock on every record in the file.
    1,930 pointsBadges:
    report
  • TomLiotta
    If defaults haven't changed, then the CRTSQLRPGI parm that is probably in control is COMMIT(*CHG). That's the default value, and it doesn't appear in your command -- so that's how the program should have compiled. Your first UPDATE statement updated every row in the table. But there was no COMMIT before the second UPDATE. The second UPDATE wanted to update every row in the table, but the changes hadn't been committed yet. So, the rows were all locked. You could try placing COMMIT statements after each of your original UPDATE statements to see if locks are released. And you should have a COMMIT after the single UPDATE statement in the revised version, too. There are alternatives, but you might want to see if you can confirm what might be happening. If nothing else, it should eliminate a bunch of possibilities. Tom
    125,585 pointsBadges:
    report
  • aceofdelts
    Committ seems like it will solve my issue Do you know the syntax off the top of your head ? If not, I can certainly look it up As always, I'll let you know all is well once it actually works thanks in advance, Mike
    1,930 pointsBadges:
    report
  • TomLiotta
    It'll look something like this:
    C/exec sql
    + COMMIT
    C/end-exec
    That's about all there is. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Be aware that it's guesswork so far. Maybe the biggest clue is that straight RPG works, but changing to SQL RPG doesn't. When you change from native to a SQL environment, you do more than just run SQL statements in the program. You also activate the standard behaviors of "SQL". For example, SQL expects your tables to be journaled and that commitment control will be started. The CRTSQLRPGI command defaults to creating a program that runs under commitment control -- COMMIT(*CHG) rather than COMMIT(*NONE). You might compile with COMMIT(*NONE) and skip over this problem. Or you might add "WITH NC" to the end of your UPDATE statement. But if you are going to update all rows in the table, committing the changes is strongly recommended. Doing things with SQL involves the whole run-time environment. Keep that in mind whenever you move from native to SQL. (Even if this problem turns out to be something else.) Tom
    125,585 pointsBadges:
    report
  • aceofdelts
    It works ! And fast (somewhat the point of doing the SQL update in the first place). Thanks again, Mike
    1,930 pointsBadges:
    report
  • PGMBOB
    Look at the ENVIRONMENT statement in SQL. You can control comittment control in case the program is re-compiled with -out the right parms.
    1,085 pointsBadges:
    report
  • aceofdelts
    [...] out the great discussion and TomLiotta’s approved answer to a member’s question about CPF5032, SQL RPG, and unlocking a data file to avoid halt. 6. Do you know if Windows Server 2000 supports Integration Services Hyper-V VSS [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following