By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

SQLDA problem - data type doesnt match

P: 16
Hello guys,

Pls dont laugh - it is not funny (actually it is, but it is causing me problems)

I have problem with SQLDA data type specification.

I need to create some counted columns, so I decided to write a trigger (or set of triggers - we will see) to count for me automatically.

I added the column where I would like to have results using command:

db2 "ALTER TABLE <table> ADD CPU_COUNTED INTEGER" since I am sure that the columns affected by trigger are in INTEGER format.

then I tried to create trigger:

CREATE TRIGGER PROC_COUNTER
AFTER INSERT ON <table>
FOR EACH ROW
UPDATE <table>
BEGIN
SET CPU_COUNTED =
CASE
WHEN PART_NBR_OF_CP = 0
THEN 'NBR_OF_CP'
WHEN PART_NBR_OF_CP != 0
THEN 'PART_NBR_OF_CP'
END;

... and I have an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0408N A value is not compatible with the data type of its assignment target. Target name is "CPU_COUNTED". LINE NUMBER=2. SQLSTATE=42821

so - I tried to release DESCRIBE SELECT command to check and I think I found the problem:

The table I am creating with "IMPORT ....create into" statement made these columns (taken from describe):

SQLDA Information

sqldaid : SQLDA sqldabc: 8068 sqln: 183 sqld: 183

Column Information

sqltype sqllen sqlname.data sqlname.length
-------------------- ------ ------------------------------ --------------
496 INTEGER 4 NBR_OF_CP 9
496 INTEGER 4 PART_NBR_OF_CP 14
added column:
497 INTEGER 4 CPU_COUNTED 11

Maybe it is caused that the table is originally created and hosted by z/OS (mainframe) and I have Linux Red Hat ... SO - IS THERE ANY WAY HOW TO SPECIFY THE SQLTYPE FOR INTEGER TO HAVE ALL 496?

.... no - still it is not funny :)
Apr 16 '08 #1
Share this Question
Share on Google+
2 Replies


P: 16
Dynamic SQL solved the pb ...
May 12 '08 #2

P: 57
Pramanwulan,
The case statement in the trigger is trying to assign the name of a column as a string to the integer field - lose the quotes and you should be fine.

As for the SQLDA codes: 496 and 497 are both integers, however the 497 is for nullable columns (the default). Add NOT NULL to the column when adding it to the table with the ALTER statement.
May 15 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.