Hi,
During the execution of a target publication (DB2), in monitoring the DB2 trace, we found different errors:
- SQLCODE = -204, ERROR: PBCATOWN.PBCATTBL IS AN UNDEFINED NAME
- SQLCODE = -204, ERROR: SYSCAT.CONSTDEP IS AN UNDEFINED NAME
- SQLCODE = -204, ERROR: SYSCAT.COLUMNS IS AN UNDEFINED NAME
- SQLCODE = -206, ERROR: BASE_SCHEMA IS NOT VALID IN THE CONTEXT
- SQLCODE = -514, ERROR: THE CURSOR SQL_CURSH200C4 IS NOT IN A PREPARED STATE
- SQLCODE = -553, ERROR: CCEUF1CO SPECIFIED IS NOT ONE OF THE VALID AUTHORIZATION IDS FOR REQUESTED OPERATION
Analyzing the error -204 specifically, we notice that the datamaker performs an incorrect query that is not present in any of our mappings, by querying unknown DB2 objects.
Example scenario:
Our maps have been implemented following this standard:
@execsql (S,
SELECT ID
FROM ~ CD_SCHEMA_LOCATION ~ .TABLE_NAME
WHERE NAME LIKE '% ~ VARIABLE_NAME ~%'
) @
S -> DB2 source.
~ CD_SCHEMA_LOCATION ~ -> Variable that identifies the schema (EXAMPLE: CCEUT1CO)
~ VARIABLE_NAME ~ -> Variable that sets TABLE_NAME.NAME
The trace reports the following error every time a query is executed as the example above:
SQLCODE = -204, ERROR: PBCATOWN.PBCATTBL IS AN UNDEFINED NAME
------------------------------- SQL TEXT ----------------------------
select pbd_fhgt, pbd_fwgt, pbd_fitl, pbd_funl, pbd_fchr, pbd_fptc,
pbd_ffce, pbh_fhgt, pbh_fwgt, pbh_fitl, pbh_funl, pbh_fchr,
pbh_fptc, pbh_ffce, pbl_fhgt, pbl_fwgt, pbl_fitl, pbl_funl,
pbl_fchr, pbl_fptc, pbl_ffce, pbt_cmnt
from PBCATOWN.pbcattbl
where pbt_tnam = 'TABLE_NAME'
and pbt_ownr = 'CCEUT1CO'
FOR FETCH ONLY
Do you have any idea why datamaker executes our query script on those objects?
In the TRACE log there are similar errors related to querying other DB2 objects:
SQLCODE = -204, ERROR: SYSCAT.CONSTDEP IS AN UNDEFINED NAME
------------------------------- SQL TEXT ----------------------------
SELECT BNAME
FROM SYSCAT.CONSTDEP
WHERE CONSTNAME = 'TABLE_NAME_2_pk'
AND TABSCHEMA = 'CCEUT1CO'
AND TABNAME = 'TABLE_NAME_2'
AND BTYPE = 'I'
FOR FETCH ONLY
SQLCODE = -204, ERROR: SYSCAT.COLUMNS IS AN UNDEFINED NAME
------------------------------ SQL TEXT ----------------------------
SELECT c.colname, c.colno + 1, c.generated, ci.start
FROM syscat.columns c, syscat.colidentattributes ci
WHERE c.tabschema = ci.tabschema
AND c.tabname = ci.tabname
AND c.colname = ci.colname
AND c.tabname = 'TABLE_NAME_2'
AND c.tabschema = 'CCEUT1CO'
AND c.identity = 'Y'
FOR FETCH ONLY
SQLCODE = -206, ERROR: BASE_SCHEMA IS NOT VALID IN THE CONTEXT
------------------------------- SQL TEXT ----------------------------
SELECT base_schema, base_name
FROM sysibm.systables
WHERE creator = 'CCEUT1CO'
AND name = 'TABLE_NAME_3'
AND type = 'A'
FOR FETCH ONLY
SQLCODE = -514, ERROR: THE CURSOR SQL_CURSH200C4 IS NOT IN A PREPARED STATE
------------------------------ SQL TEXT ----------------------------
select pbd_fhgt, pbd_fwgt, pbd_fitl, pbd_funl, pbd_fchr, pbd_fptc,
pbd_ffce, pbh_fhgt, pbh_fwgt, pbh_fitl, pbh_funl, pbh_fchr,
pbh_fptc, pbh_ffce, pbl_fhgt, pbl_fwgt, pbl_fitl, pbl_funl,
pbl_fchr, pbl_fptc, pbl_ffce, pbt_cmnt
from PBCATOWN.pbcattbl
where pbt_tnam = 'TABLE_NAME'
and pbt_ownr = 'CCEUT1CO'
FOR FETCH ONLY
...
Thank you waiting for feedback.
Best Regards
Giulio