What does a *SQLPKG do?

[jtaylor___]

I have a client creating *SQLPKG objects, and I need to know what they do. The IBM docs recommend PRTSQLINF, but that doesn't seem to tell me what it actually does.


Here's a sample of the output from PRTSQLINF:
Object name...............QGPL/SSMSAFBA
Object type...............*SQLPKG
CRTSQL***
PGM(QGPL/SSMSAFBA)
SRCFILE( / )
SRCMBR( )
COMMIT(*NONE)
OPTION(*SQL *EXTIND *PERIOD)
TGTRLS(*PRV)
ALWCPYDTA(*OPTIMIZE)
CLOSQLCSR(*ENDPGM)
DECRESULT(31 31 0)
STATEMENT TEXT CCSID(37)

[godhajar33]

Try this: http://www-01.ibm.com/support/docvie...d=nas8N1019633

[B.Hauser]

An SQL Package includes the access plans for the SQL statements. When execting an SQL statement a full open occurs (at least for the first time the SQL statement is run in an activation group/job).
With the full open existing access plans are validated or created by scratch, before the ODP (open data path) can be opened.
The execution might be faster if an existing access plan can be validated.
But since SQE was introduced, the SQE plan cache is searched first. If one is found the SQL Packages are not searched.

Birgitta

[jtaylor___]

So a SQL Package doesn't contain actual code, just access plans?

[arrow483]

Correct. You can delete *SQLPKG objects with no harm (except maybe slightly longer run time then next time you run it).

[tomliotta]

So a SQL Package doesn't contain actual code, just access plans?

Well... yes and no. There are at least two "types" of *SQLPKG objects. One visible difference can be seen in a 'Work with Objects' list where one type will have an 'Attribute' of "PACKAGE" and the other will have none.

If you run CRTSQLPKG over a SQLRPGLE program (or SQLCBLLE, etc.), the resulting *SQLPKG will show as 'Attribute' of "PACKAGE" and contain SQL statements from the program. But most/all packages auto-created seem not to contain human-readable code, though it might depend on the client and connection.

[jtaylor___]

Well... yes and no. There are at least two "types" of *SQLPKG objects. One visible difference can be seen in a 'Work with Objects' list where one type will have an 'Attribute' of "PACKAGE" and the other will have none.

If you run CRTSQLPKG over a SQLRPGLE program (or SQLCBLLE, etc.), the resulting *SQLPKG will show as 'Attribute' of "PACKAGE" and contain SQL statements from the program. But most/all packages auto-created seem not to contain human-readable code, though it might depend on the client and connection.

All but one of the *SQLPKG objects in question have a blank Attribute. The one with the PACKAGE Attribute still doesn't seem to show any code however.

[tomliotta]

All but one of the *SQLPKG objects in question have a blank Attribute. The one with the PACKAGE Attribute still doesn't seem to show any code however.

I might clarify that "contains" meant to me that SQL might be seen in a dump of the object. However, this is output from PRTSQLINF from one such object on one of my systems:

 5761SS1 V6R1M0 080215     Print SQL information          SQL package *LIBL/UPDBYVAL                  04/19/17 02:11:17  Page   001
 Object name...............*LIBL/UPDBYVAL
 Object type...............*SQLPKG
   CRTSQL***
       PGM(TOML/UPDBYVAL)
       SRCFILE(TOML/QRPGLESRC)
       SRCMBR(UPDBYVAL)
       COMMIT(*CHG)
       OPTION(*SYS *NOEXTIND *PERIOD)
       TGTRLS(V6R1M0)
{ irrelevant lines snipped }
       SQLPATH(*LIBL)
       DECRESULT(31 31 0)
       DECFLTRND(*HALFEVEN)
       STATEMENT TEXT CCSID(37)
 UPDATE FILE1 SET FIELD1 = 'X' , FIELD2 = 'Y' WHERE FIELD3 < 58 AND FIELD4 =
     CURDATE ( ) AND FIELD5 = :MYINPUT
   SQL5065  Access plan not found.
                                * * * * *  E N D  O F  L I S T I N G  * * * * *

At the end, the SQL UPDATE statement is shown. Also, message SQL5065 says that there is no "access plan" in that SQL package. IOW, the rules aren't quite completely clear yet on what one "contains". With enough examples, I suppose we can work the rules out.

When I look at each package (in *USRLIBL) on that system, they all follow one set of rules that can be anticipated by seeing if an 'Attribute' value exists. It's apparently a bit more complex, but I'd have to dig into perhaps many more packages.

SQL statements do appear in some packages. It's just not clear when/why.