How to resolve ERROR - SQL STATEMENT GREATER THAN 65530 BYTES

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• Xpertware

Issue/Introduction

After making changes to an existing query, this error is produced when it is run:

ERROR - SQL STATEMENT GREATER THAN 65530 BYTES
SQL STATEMENT EXCEEDS 65530 CHARACTER(S) 

How do we resolve this error?

Environment

Release: DATABB00200-14-Datacom/AD
Component:

Resolution

There is a limit to the size of a single query to be processed within CA Datacom, and this limit of 65530 cannot be changed without some major architectural changes to the base CA Datacom/DB product. 

However, we have a few suggestions that might help you to make your query fit this limit: 

1. We remove all trailing spaces from each line, but we do not remove leading spaces. Shifting your query text to the left as much as possible will reduce the size of your query; 
2. If you are using table names with every column being referenced, you should consider correlations. For example if you are using authId.tblName.colName1, authId.tblName.colName2, authId.tblName.colName3, you can use the correlation name on the table reference, resulting in a shorter query: corrNm.colName1, corrNm.colName2, corrNm.colName3;
3. If you are using a SELECT with a lot of OR conditions, you might be able to replace them with an IN clause. 
4. If you have a SELECT with a large number of columns, you might replace them with a SELECT *. This will return more data than you need, but will allow a lot more room for the other clauses. 

Additional Information

As always, please contact CA Technologies support for CA Datacom if you have further questions.