0

I'm experimenting with a IBM DB2 on z/OS (using the IBM Cloud Wazi / Virtual server instances (s390x) and running some queries directly using DBeaver and IBM Data Studio.

I want to execute a simple SQL PL script directly from a SQL Script, like I do on my DB2 LUW and other database.

BEGIN
DECLARE SQLCODE INTEGER;

SELECT COUNT(*) INTO SQLCODE FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND TRIM(CREATOR) = 'IBMUSER' AND TRIM(NAME) = 'BOOKS';

IF SQLCODE = 1 THEN
    EXECUTE IMMEDIATE 'DROP TABLE IBMUSER.BOOKS';
END IF;
END

After executing it directly returns all kinds of error. Like the following;

[Code: -104, SQL State: 42601]  ILLEGAL SYMBOL "SQLCODE". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11]

Am I correct that I cannot directly sent SQL PL code from a sql window/client tool to the database? I've got it working by creating a stored procedure and use the CALL functionality, but I would prefer not to create all kinds of stored procedures?

Or can I configure the database in such way that I can use SQL PL directly from sql scripts.

Bert
  • 43
  • 1
  • 4
  • I've changed it into v_count and I got the same error only than with v_count. My IBMUSER is DBADM on the database. – Bert Apr 04 '23 at 14:43
  • Do you have access to a fat-db2-client (i.e. not jdbc). I believe CLI/odbc supports BEGIN COMPOUND ... END COMPOUND, Might be worth a check. – mao Apr 04 '23 at 15:36
  • 1
    It appears that BEGIN COMPOUND ... END COMPOUND only supports DML, but not SQL PL , so for Db2-for-Z/OS it seems you can only use SQL PL in routines and in triggers (at currently available versions). – mao Apr 04 '23 at 16:29

1 Answers1

0

This is not possible on DB2 z/OS directly from a SQL statement. SQL PL can only be executed as part of a STORED procedure.

You can execute the stored procedure from SQL using CALL

Bert
  • 43
  • 1
  • 4