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)

and

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.

… Eric