How to lock a file for SQL update

[JustinWithoutAnE]

I have a CL program that runs 2 SQL statements to update a file but there is a succeeding program that also updates the same file. The succeeding program has gone into LCKW because of the SQL program, I think. Should I just put ALCOBJ to make sure the file is locked to my program and the next program has to wait for my program to finish? This is my first time to use SQL to update in a CL program.


I used RUNSQL to execute both UPDATE statements.
Is there a more efficient way to do this?

[Scott Klement]

Are you using commitment control? (Check the COMMIT parameter of RUNSQL -- it defaults to *CHG). If you are, you'll need to make sure you commit your changes.

[JustinWithoutAnE]

Is it better to use COMMIT(*ALL)?

RUNSQL SQL('UPDATE FILE1 SET DATA2 = +
CONCAT(CONCAT(SUBSTRING(DATA2, 1, 154), +
''07''), SUBSTRING(DATA2, 157, 469)) +
WHERE NUMBER IN (SELECT B.NUMBER FROM +
FILE2 B)') COMMIT(*ALL)

[B.Hauser]

The commitment level *CHG is fine, but you need to execute COMMIT to confirm the update and free the rows. (If a ROLLBACK is performed the locked data is reset and freed).
Check also whether the Update and Commit are executed within the same activation group.

If you are using commit *ALL you should really know what you do, because the rows get even locked when reading them.

Birgitta

[JustinWithoutAnE]

How do I execute commit? Is this correct?

RUNSQL     SQL('COMMIT')

Or should I use this?

RUNSQL SQL('UPDATE FILE1 SET DATA2 = + CONCAT(CONCAT(SUBSTRING(DATA2, 1, 154), + ''07''),
SUBSTRING(DATA2, 157, 469)) + WHERE NUMBER IN (SELECT B.NUMBER FROM + FILE2 B)')
COMMIT(*NONE)

[B.Hauser]

If you specify COMMIT(*NONE) in your RUNSQL command, you do not need any commit since the SQL Command is NOT executed under commitment control!
Also there is a CL command COMMIT.