I’m working with IBM DB2 on z/OS using DBeaver to run queries. I want to run SQL PL blocks directly in my SQL client without having to create stored procedures first.
Here’s what I’m trying to do:
BEGIN
DECLARE record_count INTEGER;
SELECT COUNT(*) INTO record_count FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T' AND TRIM(CREATOR) = 'MYUSER' AND TRIM(NAME) = 'CUSTOMERS';
IF record_count = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE MYUSER.CUSTOMERS';
END IF;
END
But I keep getting syntax errors like this:
[Code: -104, SQL State: 42601] ILLEGAL SYMBOL "record_count". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601
I can make this work by wrapping it in a stored procedure and calling it, but that’s not ideal for my workflow. Is there a way to execute SQL PL statements directly in DB2 z/OS without creating procedures? Maybe some database configuration or different approach I should try?
You’re attempting to execute an SQL PL block directly within DBeaver when connected to IBM DB2 on z/OS, encountering a [Code: -104, SQL State: 42601] ILLEGAL SYMBOL "record_count". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601 error. Your goal is to conditionally drop a table based on whether it exists, without resorting to creating a stored procedure.
TL;DR: The Quick Fix:
DB2 z/OS doesn’t directly support anonymous PL/SQL blocks in the way other database systems might. The most straightforward approach involves using dynamic SQL within a single statement to achieve the conditional drop. Instead of a PL/SQL block, try this:
EXECUTE IMMEDIATE
CASE
WHEN EXISTS (SELECT 1 FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' AND TRIM(CREATOR) = 'MYUSER' AND TRIM(NAME) = 'CUSTOMERS') THEN
'DROP TABLE MYUSER.CUSTOMERS'
ELSE
'SELECT ''Table does not exist'' AS MESSAGE' --or any appropriate action
END;
Understanding the “Why” (The Root Cause):
The error "ILLEGAL SYMBOL "record_count" arises because DB2 z/OS’s SQL parser, when encountering a direct BEGIN...END block within a query, expects standard SQL statements, not procedural logic. Unlike some other DB2 flavors (like LUW), it doesn’t inherently support executing anonymous PL/SQL blocks outside of stored procedures. The CASE statement approach above cleverly uses dynamic SQL to execute the DROP TABLE command only if the table exists, circumventing the need for a full PL/SQL block.
Common Pitfalls & What to Check Next:
Authorization: Ensure your DBeaver user has the necessary privileges (DROP TABLE authority) on the MYUSER.CUSTOMERS table.
Case Sensitivity: DB2 z/OS is case-sensitive. Verify the capitalization of MYUSER and CUSTOMERS matches exactly the table’s definition in SYSIBM.SYSTABLES.
Schema Qualification: If the CUSTOMERS table exists in a schema other than MYUSER, adjust the TRIM(CREATOR) and TRIM(NAME) clauses in the EXISTS check accordingly. If it’s in the default schema, you might omit the TRIM(CREATOR) altogether, depending on your DB2 configuration.
Error Handling: While the provided CASE statement handles the absence of the table gracefully, for more robust error handling in production code, explicitly check SQLCODE after EXECUTE IMMEDIATE to catch potential issues like insufficient privileges or other database errors. Consider using a TRY...CATCH block if your tools support it within dynamic SQL execution.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!
DB2 z/OS doesn’t support anonymous blocks or running SQL PL code outside stored procedures. That’s just how the platform works - it’s different from other DB2 versions like LUW. You’re getting that syntax error because the SQL parser expects regular SQL statements, not procedural logic. Your best bet is creating temp stored procedures and dropping them right after, or restructuring with conditional SQL statements. For what you’re trying to do, you could use a single dynamic DROP statement with error handling through SQLCODE checking instead of the procedural approach.
yea, db2 z/os has this annoying limitation with anonymous blocks. u basically gotta use stored procs. but ya could try dynamic sql for a temp proc just for that. it’s a bit of a hassle, but it works.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!