SQL Errors with Generated UNLOAD and DELETE SQL Statements

Article Id: 7992

Status: Published

Updated On: 14-02-2018 08:18

Legacy Id: TEC1897265

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.