Hi,
does anybody know how to test in script files if values already exists in a table(s), please?

I have following data.sql script:
---
CONNECT TO MyDB;
INSERT INTO APPCONFIG (KEY, VALUE) VALUES ('someKey', 'someValue');
CONNECT RESET;
---
(APPCONFIG is my custom table)

I run this script using DB2 command line tool:
---
DB2 -tf data.sql
---

My problem is, that this script may be run more than once. So i need to add some IF statement to test if the key alerady exists. I've tried a lot of IF SELECT COUNT(*) FROM ... statements, but I still receive syntax errors etc.

Can anybody help me, please?

What DB2 version and OS?

Andy

Sorry, sure - DB2 v9.5.0.808 (Express), OS Win 2003 Server
(I thought the language is version and OS independent).

I wanted the version info so I know what features are available to you.

Why do you want to test the existence first? Why not just let it fail?

Andy

Thank you for your reply.

The KEY column is not a PK, there is also ID (generated as identity) and the code will not fail, but insert new row into the table. Sure, in this scenario the KEY could be a PK, but I have also tables as USERS, ROLES, EMPLOYEES etc that I also need to pre-fill by the same script.

Take a look at the MERGE statement to use instead of the INSERT statement.

Andy

I alraedy did, but it seems to me the MERGE statement meges data from another /source/ table, not from defined values, doesn't?

Thanks a lot!

Frantisek.

You can use MERGE from defined values, something like:


MERGE into MyTable as m using (values (cast('somekey' as varchar(12)) as KEY, cast('somevalue' as varchar(20)) as VALUE) ) as x on (m.key = x.key)
WHEN NOT MATCHED THEN insert...


Andy

if statements are not allowed in db2 scripts
on unix use shell scripting to do or on windows any other supported language todo so. there is something like dos scripting.. or perl..
or use a stored procedure

Andy's code must be syntax error of naming columns in values clause.
Correction would like:

MERGE into MyTable as m using (values (cast('somekey' as varchar(12)), cast('somevalue' as varchar(20))) ) as x(key, value) on (m.key = x.key)
WHEN NOT MATCHED THEN insert...

If you have a unique constraint on the KEY column, just ignore the error. If you have not a unique constraint, create it.

I think you can you use the Begin atomic compound stmts in scripts

That's it! Thank you both.

I've solved my primary problem by MERGE statement, but I'm newbie in DB2 world, can you please explain what do you mean by BEGIN ATOMIC statement?

Thanks a lot!

You can conditional stmts in a begin atomic script as :

/TEMP 21$ cat 1.txt
Begin Atomic

if ( select count(*) from dim.test4 ) = 0
then
insert into dim.test4 values (4);
else
update dim.test4 set no=no+10 ;
end if;

end @

/TEMP 24$ db2 -td@ -f 1.txt
DB20000I The SQL command completed successfully.