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 :)