APAR status
Closed as program error.
Error description
For REXX applications - If LOBS/DISTINCT EXIST with USING BOTH: 1) REXX will now double the SQLD and return it to the user and the LABELS will be returned after the columns just as if there were no LOBs. 2) Return the Distinct type in the SQLTNAME.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 and 13 for z/OS users of REXX. * **************************************************************** * PROBLEM DESCRIPTION: * * For a REXX application that issues a * * PREPARE INTO which includes a LOB * * column, the following errors will * * occur in the SQLDA. * * 1. The CCSID of the LOB is not stored * * in the SQLCCSID. * * 2. If USING BOTH and the columns are * * defined with LABELS, the SQLD is not * * doubled where the labels are not * * returned in the second set after the * * columns. * * Also, if a distinct types exists, the * * name is not returned in SQLTNAME. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** For a REXX application that issues a PREPARE INTO which includes a LOB column, the following errors will occur in the SQLDA. 1. The CCSID of the LOB is not stored in the SQLCCSID. 2. If USING BOTH and the columns are defined with LABELS, the SQLD is not doubled where the the labels are not returned in the second set after the columns. Also, if a distinct type exists, the name is not returned in SQLTNAME. Below is a an example that will illustrate the problem. 1. Create table with a LOB column and a distinct type CREATE DATABASE LI637DB CCSID UNICODE; CREATE TABLESPACE LI637TS IN LI637DB; CREATE TYPE MONEY AS BLOB(1M); CREATE TABLE LI637TB(R0 ROWID NOT NULL GENERATED ALWAYS, CH1 CHAR(1), BM2 MONEY, CH3 CLOB(100) INLINE LENGTH 100, CH4 CLOB(300)) IN LI637DB.LI637TS; 2. Create a LABEL on each of the columns in the table LABEL ON LI637TB (R0 IS 'A ROWID', CH1 IS 'ONE CHAR', BM2 IS 'MONEY TYPE', CH3 IS 'A CLOB100 INLINE', CH4 IS 'A CLOB300'); 3. Create a REXX application with the following statement and code to display SQLDA with the USING BOTH option (just a snippet of code) ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1" STMT= "SELECT * FROM LI637TB ORDER BY CH1" ADDRESS DSNREXX "EXECSQL PREPARE S1 INTO :SQLDA USING BOTH FROM :STMT" numcols = SQLDA.SQLD SAY "Number of entries: "numcols DO i = 1 TO numcols SAY 'Entry #: 'i SAY 'SQLNAME = 'SQLDA.i.SQLNAME SAY 'SQLTYPE = 'SQLDA.i.SQLTYPE SAY 'SQLLEN = 'SQLDA.i.SQLLEN SAY 'SQLLONGL= 'SQLDA.i.SQLLONGL SAY 'SQLCCSID= 'SQLDA.i.SQLCCSID SAY 'SQLTNAME= 'SQLDA.i.SQLTNAME END 4. Display results (here are the incorrect results with only 5 entries and no labels). Also, distinct type name does not exist and the CCSID for the LOBs aren't displayed. Number of entries: 5 Entry #: 1 SQLNAME = R0 SQLTYPE = 904 SQLLEN = 40 SQLLONGL= SQLDA.1.SQLLONGL SQLCCSID= SQLDA.1.SQLCCSID SQLTNAME= SQLDA.1.SQLTNAME Entry #: 2 SQLNAME = CH1 SQLTYPE = 453 SQLLEN = 1 SQLLONGL= SQLDA.2.SQLLONGL SQLCCSID= 1208 SQLTNAME= SQLDA.2.SQLTNAME Entry #: 3 SQLNAME = BM2 SQLTYPE = 405 SQLLEN = SQLDA.3.SQLLEN SQLLONGL= 1048576 SQLCCSID= SQLDA.3.SQLCCSID SQLTNAME= SQLDA.3.SQLTNAME Entry #: 4 SQLNAME = CH3 SQLTYPE = 409 SQLLEN = SQLDA.4.SQLLEN SQLLONGL= 100 SQLCCSID= SQLDA.4.SQLCCSID SQLTNAME= SQLDA.4.SQLTNAME Entry #: 5 SQLNAME = CH4 SQLTYPE = 409 SQLLEN = SQLDA.5.SQLLEN SQLLONGL= 300 SQLCCSID= SQLDA.5.SQLCCSID SQLTNAME= SQLDA.5.SQLTNAME 5. Display results (here are the correct results where the SQLD was doubled to include the labels). Now the distinct type is displayed in SQLTNAME and the CCSID for the LOBs are displayed. Number of entries: 10 Entry #: 1 SQLNAME = R0 SQLTYPE = 904 SQLLEN = 40 SQLLONGL= SQLDA.1.SQLLONGL SQLCCSID= SQLDA.1.SQLCCSID SQLTNAME= Entry #: 2 SQLNAME = CH1 SQLTYPE = 453 SQLLEN = 1 SQLLONGL= SQLDA.2.SQLLONGL SQLCCSID= 1208 SQLTNAME= Entry #: 3 SQLNAME = BM2 SQLTYPE = 405 SQLLEN = SQLDA.3.SQLLEN SQLLONGL= 1048576 SQLCCSID= SQLDA.3.SQLCCSID SQLTNAME= ADMF001.MONEY Entry #: 4 SQLNAME = CH3 SQLTYPE = 409 SQLLEN = SQLDA.4.SQLLEN SQLLONGL= 100 SQLCCSID= 1208 SQLTNAME= Entry #: 5 SQLNAME = CH4 SQLTYPE = 409 SQLLEN = SQLDA.5.SQLLEN SQLLONGL= 300 SQLCCSID= 1208 SQLTNAME= Entry #: 6 SQLNAME = A ROWID SQLTYPE = 0 SQLLEN = 0 SQLLONGL= SQLDA.6.SQLLONGL SQLCCSID= SQLDA.6.SQLCCSID SQLTNAME= SQLDA.6.SQLTNAME Entry #: 7 SQLNAME = ONE CHAR SQLTYPE = 0 SQLLEN = 0 SQLLONGL= SQLDA.7.SQLLONGL SQLCCSID= SQLDA.7.SQLCCSID SQLTNAME= SQLDA.7.SQLTNAME Entry #: 8 SQLNAME = MONEY TYPE SQLTYPE = 0 SQLLEN = 0 SQLLONGL= SQLDA.8.SQLLONGL SQLCCSID= SQLDA.8.SQLCCSID SQLTNAME= SQLDA.8.SQLTNAME Entry #: 9 SQLNAME = A CLOB100 INLINE SQLTYPE = 0 SQLLEN = 0 SQLLONGL= SQLDA.9.SQLLONGL SQLCCSID= SQLDA.9.SQLCCSID SQLTNAME= SQLDA.9.SQLTNAME Entry #: 10 SQLNAME = A CLOB300 SQLTYPE = 0 SQLLEN = 0 SQLLONGL= SQLDA.10.SQLLONGL SQLCCSID= SQLDA.10.SQLCCSID SQLTNAME= SQLDA.10.SQLTNAME
Problem conclusion
Db2 has been modified to return the correct results in the SQLDA during the PREPARE for LOB columns for both SQLCCSID and LABEL if USING BOTH option is chosen along with distinct type in the SQLTNAME.
Temporary fix
Comments
APAR Information
APAR number
PH52852
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2023-02-24
Closed date
2023-05-01
Last modified date
2023-06-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI91653 UI91654
Modules/Macros
DSNTZSDA
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI91653
UP23/05/10 P F305
RD10 PSY UI91654
UP23/05/10 P F305
[{"Business Unit":{"code":"BU011","label":"Systems - zSystems software"},"Product":{"code":"SG19O","label":"DB2 for z\/OS"},"Platform":[{"code":"PF054","label":"z Systems"}],"Version":"12.0"}]
Document Information
Modified date:
02 June 2023