SQLDA problem - data type doesnt match

[Parnamwulan]

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_COUNTE D". 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 :)

[Parnamwulan]

Dynamic SQL solved the pb ...

[cburnett]

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.