AnsweredAssumed Answered

Incorrect datamaker query launched on DB2

Question asked by GiulioChirico82365923 on Feb 22, 2019

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

Outcomes