I'm writing a Stored Procedure (SQL / PL - DB2 V11 or later) and I need to access the SYSIBM.SYSCOLUMNS to retrieve the columns of 'HELLO_WORLD' table.
On SYSIBM.SYSCOLUMNS the table 'HELLO_WORLD' is present with different TBCREATOR.
TBCREATOR | TBNAME | NAME
OWN_SV | HELLO_WORD | COLN1
OWN_SV | HELLO_WORD | COLN2
OWN_SV | HELLO_WORD | COLN3
OWN_CL | HELLO_WORD | COLN1
OWN_CL | HELLO_WORD | COLN2
OWN_CL | HELLO_WORD | COLNA
I won't to use the DISTINCT statement because the columns could be different. I thought I would use the value in the special register CURRENT_SCHEMA but unfortunately not correct.
Example:
SELECT C.NAME, C.COLNO
FROM SYSIBM.SYSCOLUMNS C
WHERE C.TBCREATOR = CURRENT_SCHEMA
AND C.TBNAME = 'HELLO_WORLD'
ORDER OF C.COLNO
The value present in the CURRENT_SCHEMA it's incorrect: if the Stored Procedure is called by another process (for example: A COBOL process) the CURRENT_SCHEMA is related to the process that verified the call.
The process that call my stored procedure "settings" CURRENT_SCHEMA with value "USERBTC" (which is the user who executed the COBOL process via JCL)
My OWNER/TBCREATOR is "OWN_CL" and this value is specified in the bind parameters (more precisely, the parameter 'QUALIFIER')
To be sure of getting the table correctly I should use the QUALIFIER (OR OWNER) specified in the BIND options.
It's possible? Do you have any suggestions?