This may seem a bit paradoxical, but is there a special SQL command to target and execute an existing statically bound SQL package through the Command Editor or any other SQL client?

I noticed that when using the static profiling tool in match mode, if you create your JDBC connection with an auth id that does not have permission to execute the statically bound SQL, you get an error messsage saying something like:

"USER" does not have the privilege to perform operation "EXECUTE PACKAGE" on object "MYSCHEMA.MYPACKAGE1"

where '1' is the section number.

This led me to believe that perhaps EXECUTE PACKAGE was a valid DB2 SQL command, which does not appear to be the case.

i think you are forgetting that static sql,
which when bound creates a package, which is a db2 object.
db2 objects have attributes, one of which is the privilege(authority) to execute/access......
user requires privilege to execute the package,
same as user requires the privilege (authority) to access any other db2 object, like a table.

dbzTHEdinosaur,

Thank you for the reply, and I can see where my original post was a little misleading. The error about the lack of privileges was corrected by changing which user I authenticated with and it did run statically thereafter. I only mentioned the error because it clued me in to the possibility that I could invoke statically bound SQL through some command (with the right auth id).

More specifically, I am using the JDBC type 2 driver and:

StaticMode = MATCH

I can see how the driver is matching the dynamic sql statement with the statically bound schema, package, and section number, but what is unclear to me is how it is using these three pieces of information to actually INVOKE the bound query.

To be completely honest, I am after a poor man's pureQuery here for type 4 drivers.

1. I know I can easily recreate the capture file and use the db2cap executable to perform the bind operation.
2. I know I can easily match up the dynamic sql with the schema, package, and section number.
3. How do I execute the static SQL?

You can GRANT EXECUTE ON PACKAGE, but it won't do you any good since the package must match up with the program that it was original created from (as embedded SQL).

I suspect that you would be satisfied with a UDF (more powerful than many realize in newer releases of DB2 LUW) or a stored procedure.

Marcus,

Thank you for this tidbit: "You can GRANT EXECUTE ON PACKAGE"

Now we are at the heart of the issue: "the package must match up with the program that it was original created from".

What does it mean for the package to match up with the program that it was originally created from? Is the type 2 driver in MATCH StaticMode sending the CONTOKEN or some other piece of data in a hidden and proprietary way to DB2 prior to executing the package? Is there no equivalent in the type 4 driver? Can I somehow employ a UDF to do this program-to-package handshaking before attempting to execute the package?

You cannot directly execute a package. You can execute a program that executes the package. In your case I am assuming a UDF or Stored Procedure is the best choice.

When an application program is written with embedded static SQL (including static SQL in a UDF or SP), the SQL is replaced in the DB2 pre-compiler with calls to DB2 using a package created for that program. The consistency token of the package must match to make sure the right version of the package matches the right version of the program (the program is modified by the DB2 pre-compiler to store the consistency token in the program, and of course it also exists in the package).

I don't understand what you trying to do that you can't use a UDF or SP?