Page 1 of 1
SQL using DataStage Administrator
Posted: Tue Nov 22, 2005 1:00 am
by 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
Re: SQL using DataStage Administrator
Posted: Tue Nov 22, 2005 1:09 am
by loveojha2
nvkuriseti wrote: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.
Posted: Tue Nov 22, 2005 1:24 am
by nvkuriseti
What is TBSPACEID, TABLEID, COLNO? Where can I find these columns from DataStage Administrator? Which "DS_*" will be contain?
Venkat
Posted: Tue Nov 22, 2005 1:45 am
by chulett
Talk to your DBA, those seem like DB2 specific numbers to me.
Posted: Tue Nov 22, 2005 1:47 am
by 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.
Posted: Tue Nov 22, 2005 1:49 am
by 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
nvkuriseti wrote: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.
Posted: Tue Nov 22, 2005 11:46 am
by 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,