U bent hier

BRMS breaks usage of DB2 API QZDFMDB2

Running these commands result in successful process return code:

CRTPF FILE(QTEMP/XYZ) RCDLEN(50)     

CALL PGM(QZDFMDB2) PARM('DROP TABLE QTEMP.XYZ')

(Result: "DB20000I  THE SQL COMMAND COMPLETED SUCCESSFULLY.")

While if you run these same commands, but in between go into a BRMS function (just go into the command and exit it immediately with F3 is enough), like here:

CRTPF FILE(QTEMP/XYZ) RCDLEN(50)

WRKMEDBRM

CALL PGM(QZDFMDB2) PARM('DROP TABLE QTEMP.XYZ')

That results in SQL/CLI error at the moment the DB2 API is called:

... CONTINUING.                                     
                                                    
 **** CLI ERROR *****                               
         SQLSTATE: 42977                            
NATIVE ERROR CODE: -7022                            
User SI29733* not the same as current user ZVINGENS.

... I wonder why that is...

Background info

Doing a DSPJOBLOG shows this:

2 > CALL PGM(QZDFMDB2) PARM('DROP TABLE QTEMP.XYZ')                         
    Error occurred in SQL Call Level Interface                              
    AUTHORIZATION ERROR:  REFER TO PTF 5761SS1 SI29733 FOR MORE INFORMATION.
    User SI29733* not the same as current user ZVINGENS.                    
3 >  *SYSTEM/WRKJOB                                                         

Details of the above messages are:

Error occurred in SQL Call Level Interface

Message ID . . . . . . :   SQ99999       Severity . . . . . . . :   30       
Message type . . . . . :   Diagnostic                                        
Date sent  . . . . . . :   31/07/14      Time sent  . . . . . . :   10:06:41 
                                                                             
Message . . . . :   Error occurred in SQL Call Level Interface               
Cause . . . . . :   A procedure call encountered an error.  The error code is
  10. Error codes are:                                                       

...

10 -- Function sequence error.

...

Recovery  . . . :   Refer to the DB2 for i5/OS SQL Call Level Interface (ODBC)
  topic collection in the Database category in the IBM i Information Center   
  book for a complete description of the error. Specifically, look at the     
  procedure that sent the error
.              

So I did F9=Display message details to explicitly look at the procedure that sent the error:

From program . . . . . . . . . :   QSQCLI         
  From library . . . . . . . . :     QSYS         
  From module  . . . . . . . . :     SQLSEA       
  From procedure . . . . . . . :     SQLSetEnvAttr
  From statement . . . . . . . :     12634        

AUTHORIZATION ERROR:  REFER TO PTF 5761SS1 SI29733 FOR MORE INFORMATION.

This message (comming from procedure SQLConnect in module SQLCON, program QSQCLI) does not provide many technical background info in the message meta-data:

Message ID . . . . . . :   CPF9898       Severity . . . . . . . :   40       
Message type . . . . . :   Information                                       
Date sent  . . . . . . :   31/07/14      Time sent  . . . . . . :   10:06:41 
                                                                             
Message . . . . :   AUTHORIZATION ERROR:  REFER TO PTF 5761SS1 SI29733 FOR   
  MORE INFORMATION.                                                          
Cause . . . . . :   This message is used by application programs as a general
  escape message.                                                            

But it does refer to an interesting PTF: SI29733

User SI29733* not the same as current user ZVINGENS.

Message ID . . . . . . :   SQL7022       Severity . . . . . . . :   30        
Message type . . . . . :   Diagnostic                                         
Date sent  . . . . . . :   31/07/14      Time sent  . . . . . . :   10:06:41  
                                                                              
Message . . . . :   User SI29733* not the same as current user ZVINGENS.      
Cause . . . . . :   One of the following occurred.                            
    -- User SI29733* was specified in a CONNECT statement that specified the  
  local relational database name. The user specified is not the same as the   
  current job user ZVINGENS.                                                  
    -- User SI29733* was specified in a CONNECT statement and a connection    
  using ZVINGENS already exists to the specified relational database using    
  connection method *DUW.                                                     
Recovery  . . . :   If connecting to the local relational database, change the
  statement so the user specified on the CONNECT is the same as the current   
  job user ID.                                                                
    If connecting to a remote relational database, either use the SET
CONNECTION statement to use the existing connection or end the current
connection and issue the CONNECT statement with the new user id.      

Conclusion

None, sorry. I have the impression that BRMS uses an internally known database user to connect to its DB2 storage and leaves the connection open or something, while a subsequent SQL connect in that job than wants to use that connection with the name of the current user, resulting the conflict.

Maybe a function in BRMS should exist to re-set the database connection state / configuration...? I do read in the cover letter of PTF SI29733 (which is a PTF for r6.1) that there has been a workaround by creating a data area in QSYS (==> CRTDTAARA DTAARA(QSYS/QSQCLICON) TYPE(*CHAR) LEN(7) VALUE(*PRVCHK)  ), making sure the re-connection to the database happens with the previous user, not the (different) current user. That same page also describes that it is a workaround and in fact only a cover-up of a bug in the JDBC/CLI application, quote:

JDBC and CLI applications should be changed as soon as possible to remove the dependence upon the old behavior.

So I think it should be reported to IBM in order to change something in BRMS...

IT area: