SQL Lock

[JReksodiputro@xxxxxxxxxx]

Hi all,

I have this problem that I like to share, hopefully you will be able to help.
Please bear with me.

I have this CL driver program that will:
*    prepare a work file
*    call an SQLRPGLE program that will update the work file.

It's not going to be built in QTEMP, because this program is intended to be ran
by 1 person only (that's not supposed to be hard ;), plus it's going to automate
the data transfer to PC (no QTEMP allowed).

In the CL, I allocate the work file exclusively, clear it and then I lower the
lock-level, so that it can still do the automated transfer to PC:

***********************************************************
/* Allocate object exclusively */
             ALCOBJ     OBJ((*LIBL/WQBL001WK *FILE *EXCL)) WAIT(0)

/* If it's *LOCKED, Exit. */
             MONMSG     MSGID(CPF1002) EXEC(DO)
             CHGVAR     VAR(&MSG) VALUE('The time report query +
                          process is already active. Please try +
                          again.')
             SNDPGMMSG  MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA(&MSG) +
                             TOPGMQ(*EXT) MSGTYPE(*INFO)
             GOTO       CMDLBL(ENDP)
             ENDDO

/* Clear the work file in Data Library */
             CLRPFM     FILE(WQBL001WK)

/* Once the *EXCL lock is obtained, lowering the lock-level to avoid
lock-conflict */
/* especially when running the data transfer to PC */
             ALCOBJ     OBJ((*LIBL/WQBL001WK *FILE *SHRRD)) WAIT(0)
             DLCOBJ     OBJ((*LIBL/WQBL001WK *FILE *EXCL))
/* Call bound: The Query program */
PROCESS:
             CALLPRC    PRC(TR0005R) PARM(&PARMS)

/* Deallocate the work file */
           DLCOBJ     OBJ((*LIBL/WQBL001WK *FILE *SHRRD))

             GOTO       CMDLBL(ENDP)

...
/* Program end */
 ENDP:
             ENDPGM
***********************************************************
In the SQLRPGLE I do the "EXECUTE IMMEDIATE" statement of INSERT to the work
file, which then will be transferred to PC (with automated transfer: RTOPCB.EXE
*.DTF).

 * Build the whole SQL string
C                   clear                   sqlslt
C                   movea     sqb           sqlslt
C                   eval      sqlslt = %trim(sqlslt) + ' ' +
C                                      %trim(#wherestm)

C/EXEC SQL
C+ Execute Immediate :sqlslt
C/END-EXEC
***********************************************************

The problem is when I test it, using 2 sessions (say A and B):
*    Call the program from sess A (and monitor the lock from sess B)
     ==> no lock after pgm completed.
*    Call the program again, 2nd try, from sess A.
     ==> there's an object lock left, after pgm completed, with *SHRRD.
     ==> as a result, I can't access the pgm from sess B.

Is there something that I miss?
Do I need to do something on the SQLRPGLE to "release" the lock for "EXECUTE
IMMEDIATE"?


Thanks,

Jean Reksodiputro
Harrisburg, PA