SQL using DataStage Administrator

[nvkuriseti]

Can anybody help me in the following error/warning to find out from DataStage Administrator? Help me to write SQL. I am unable to find from which metadata will be provide for the following error/warning.

Error:
<JOB NAME>..<STAGE NAME>: [IBM][CLI Driver][DB2/6000] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=279, TABLEID=11, COLNO=21" is not allowed. SQLSTATE=23502

Hope your suggestions/solutions very soon.

Venkat

[loveojha2]

Can anybody help me in the following error/warning to find out from DataStage Administrator? Help me to write SQL. I am unable to find from which metadata will be provide for the following error/warning.

Error:
<JOB NAME>..<STAGE NAME>: [IBM][CLI Driver][DB2/6000] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=279, TABLEID=11, COLNO=21" is not allowed. SQLSTATE=23502

Hope your suggestions/solutions very soon.

Venkat

Try

Code: Select all

SELECT * FROM RT_LOGnn WHERE TYPE =3 or TYPE=2

Where nn is the Job number, which you can get from

Code: Select all

SELECT JOBNO FROM DS_JOBS WHERE NAME LIKE='jobname'

and
Type=3 meaning Fatal Error
Type=4 meaning warning

Hope this is what you were looking for.

[nvkuriseti]

What is TBSPACEID, TABLEID, COLNO? Where can I find these columns from DataStage Administrator? Which "DS_*" will be contain?

Venkat

[chulett]

Talk to your DBA, those seem like DB2 specific numbers to me.

[ArndW]

nvkuriseti,

you are writing to a DB/2 table in your job and trying to put a NULL value into your data at column number 21. The metadata of DataStage don't enter into the picture.

[loveojha2]

Error:
<JOB NAME>..<STAGE NAME>: [IBM][CLI Driver][DB2/6000] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=279, TABLEID=11, COLNO=21" is not allowed. SQLSTATE=23502

What is TBSPACEID, TABLEID, COLNO? Where can I find these columns from DataStage Administrator? Which "DS_*" will be contain?

Venkat

Hey this is the description of the error saying it was expecting value for not null column, message is from the database, It's all that your database is throwing, So it's never stored/from any of the datastage tables.

[DaleK]

nvkuriseti

You can determine which column is giving you this problem by running a query agains the DB2 system tables.

for example
select *
FROM SYSIBM.SYSCOLUMNS
WHERE SYSIBM.SYSCOLUMNS.TBNAME = '<insert table name here';

You will see a column called "Name" that is the name of the column.
Plus, you will see a column called "colno", which is the column number.
You could add "and colno = 21" to your where clause and only get the info on the one column. There is another column called "nulls." It is my guess based on the error message DB2 is returning, this column is set to "N". Thus, the DB2 error message.

If nulls are valid for the column in question then talk to your DBA and have the column definition changed, if nulls aren't valid then I guess you have some work to do.

Have a great day,