SQL Errors with Generated UNLOAD and DELETE SQL Statements

Document ID : KB000007992
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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. 

Environment:
TDM 4.0DatamakerSubsetWindows 2012 ServerJCL/SQL for Mainframe DB2 V11
Cause:

The SQL statement below gives -203 SQLCODE:

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

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.