SQL Errors with Generated UNLOAD and DELETE SQL Statements

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Need to generate data copy (unload/delete/load) SQL and JCL for mainframe DB2 tables. During the unload job, the -203 SQLCODE errors is generated for some of the SELECT statements. The SQL is not generating the join syntax correctly and is receiving -203 SQL errors due to unqualified columns when columns exist on more than one of the tables involved in the join. 

Cause

The SQL statement below gives -203 SQLCODE:

select * from 
ROM.TRIPM_PRICE 
Where PA_N in 
(‘0149979178’, ‘0150034827’, ……) 

Environment

TDM 4.0DatamakerSubsetWindows 2012 ServerJCL/SQL for Mainframe DB2 V11

Resolution

The reason for the error -203 SQLCODE is because the driving table SQL is not fully qualified.  When this is the case, the column names are not assigned an alias name in the generated SQL, and a SQL error -203 is generated.

Therefore: 

select * from 
ROM.TRIPM_PRICE 
Where PA_N in 
(‘0149979178’, ‘0150034827’, ……) 

Should be :

select * from 
ROM.TRIPM_PRICE 
Where ROM.TRIPM_PRICE.PA_N in 
(‘0149979178’, ‘0150034827’, ……) 

By adding the schema name ROM.TRIPM_PRICE, the select worked.