[SOLVED] Running SQL PL on DB2 z/OS directly without stored procedures

Running SQL PL on DB2 z/OS directly without stored procedures

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.

Solution

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