Ad-Hoc SQL Statement Package Cleanup
Greetings fellow SAP on IBM i advocates,
An SQL package, for those who may not already know, are caches the database uses to store data access plans (‘programs’) built for each SQL statement by the database optimizer and executed by the database access engine on behalf of requests from SAP application servers.
An SQL package contains 10s, 100s or 1000s of entries similar to this:
STATEMENT NAME: STMT00002 SQL Statement: SELECT "CLIENT" , "DRAFT_ENTITY" , "DRAFT_KEY" , "PRINCIPAL_DRAFT_ENTITY", "PRINCIPAL_DRAFT_KEY" , "CREATED_AT" , "CREATED_BY" , "LAST_CHANGED_AT" , "LAST_CHANGED_BY" , "ACTIVE_DOCUMENT_ETAG" , "ACCESS_TYPE" , "IN_PROCESS_BY" , "IN_PROCESS_SINCE" , "KEPT_BY_USER" , "ENQUEUE_CONTEXT" , "ENQUEUE_SINCE" FROM TABLE ( /* Entity name: SDRAFT_LOCK_EXP */ "VDRAFT_LOCK_EXP" ( ? , ? , ? ) ) "SDRAFT_LOCK_EXP" WHERE "CLIENT" >= ? AND "DRAFT_ENTITY" >= ? AND "DRAFT_KEY" >? ORDER BY "SDRAFT_LOCK_EXP" ."CLIENT" , "SDRAFT_LOCK_EXP" ."DRAFT_ENTITY" , "SDRAFT_LOCK_EXP" ."DRAFT_KEY" LIMIT ? /* ISO: UR */ Access Plan: "The Program" SQL4017 Host variables implemented as reusable ODP. SQL4002 Reusable ODP sort used. SQL4003 UNION, EXCEPT, or INTERSECT. SQL4007 Query implementation for join position 1 table 2. SQL4010 Table scan access for table 2. SQL4007 Query implementation for join position 2 table 1. SQL4010 Table scan access for table 1. SQL4007 Query implementation for join position 3 table 2. SQL4008 Index SDRAF50003 used for table 2. SQL4011 Index scan-key row positioning used on table 2. SQL4007 Query implementation for join position 4 table 2. SQL4008 Index SDRAF50003 used for table 2. SQL4011 Index scan-key row positioning used on table 2. SQL4006 All indexes considered for table 0. SQL4029 Hashing algorithm used to process the grouping.
An SAP system generally utilizes hundreds of SQL packages; each one able to grow to a gigabyte. Most SQL packages contain SQL statements that are used and reused; but some SQL packages store ‘single execution’ SQL statements; those used once. These ‘single execution’ packages are created and deleted by each work process as needed… in theory. In practice, work-processes sometimes end abruptly; leaving around residual ‘single execution’ packages on the system – these are zombies.
The SQL packages in question, those storing ‘single execution’ statements, are easily identified using either of the following commands depending on the kernel release:
LSTOBJINF OBJ(R3Y*/*ALL) OBJTYPE(*SQLPKG)
LSTOBJINF OBJ(S4Y*/*ALL) OBJTYPE(*SQLPKG)
The commands produce listing similar to this example:
The SAP Kernels mentioned in SAP Note: 2495526 contain new DLTOLDPKGS programs that identify and delete residual ‘single execution’ packages. BW systems that rely significantly on ‘single execution’ SQL statements will see the greatest positive effect; potentially freeing up 100’s of Gigabytes of storage allocated to zombie packages.
If you have any further questions or concerns, please open a SAP ticket to our component BC-DB-DB4 or BC-OP-AS4 and we’d be more than happy to converse with you.