SQL PKG and CRTSQLRPGI

Posted by: Bob Cozzi

Rogue Programmer

Cozzi Productions, Inc.

www.cozTools.com/qryfile

Chicagoland

SQL PKG and CRTSQLRPGI
has no ratings.

Published: 01 Feb 2012
Revised: 23 Jan 2013 - 4103 days ago
Last viewed on: 17 Apr 2024 (8822 views)

Main URL:

SQL PKG and CRTSQLRPGI Published by: Bob Cozzi on 01 Feb 2012 view comments(4)

Is it true the specifying the SQLPKG parameter on CRTSQLRPGI does nothing?

And that in fact, you have to issue a CRTSQLPKG command after creating your RPG IV program that contains embedded SQL with remote access (CONNECT TO) statements?

I assumed that when you do a CRTRPGSQLI that the SQLPKG parameter, which defaults to SQLPKG(*OBJLIB/*OBJ) would create an SQL package with the same name as the program in the target library. But so far I've always had to do CRTSQLPKG with the program name of the program I previously created.

Is this normal?

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: DaleB

Premium member *

Reading, PA

Comment on: SQL PKG and CRTSQLRPGI
Posted: 12 years 2 months 17 days 10 hours 16 minutes ago

I've never used the SQLPKG parameter. IBM tells us we can freely delete *SQLPKG objects, because they'll be automatically recreated if/when needed. Based on that I'm thinking the presence of *SQLPKG might make the next run faster, but if it's not there, you don't lose any function. But that's just a guess. (If you do any File Transfers from Windows you wind up with *SQLPKG's, probably in QGPL, which you might want to delete every so often just on principle.)

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL PKG and CRTSQLRPGI
Posted: 12 years 2 months 10 days 9 hours 27 minutes ago

The SQL Package I'm talking about is that required on the remote system in order to have One Program that access data on Multiple IBM i systems.Windows is NOT involved.

Apparently after you create an RPG IV with embedded SQL program and you have included the CONNECT TO statement, you ALSO have to:

Run CRTSQLPKG to create an SQL package. (Using SQLPKG on the CRTSQLRPGI command doesn't do it)
Distribute the SQL Package to all remote IBM i systems that you will be connecting to.

If you recompile your SQL RPG program, then you have to perform steps 1 and 2 over again.

�

Posted by: DaleB

Premium member *

Reading, PA

Comment on: SQL PKG and CRTSQLRPGI
Posted: 12 years 2 months 10 days 7 hours 2 minutes ago

Hmm... you already know more than I do, but I find this intriguing. SQL Programming has a section Distributed relational database function and SQL, and then under that there's a section SQL package support. It does mention using CRTSQLPKG if the package is needed at an RDB other than the one specified on the precompile command, which sounds like your situation. Could be something useful in this manual.

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL PKG and CRTSQLRPGI
Posted: 12 years 2 months 10 days 6 hours 35 minutes ago

Read that manual. Some vague stuff in there.

There may be an alternative if you specify the RDB on the CRTSQLRPGi command, however since the RDB is known at runtime, not compile time, the CONNECT TO is evoked during the program. Hence...

�CONNECT TO MYREMOTE USER BOBBY PASSWORD ROSEBUD

Connects to the MyRemote as named in the ADDRDBDIRE entry.

Specify MYREMOTE for the RDB parameter at compile-time, would thus cause an issue when connecting to MYOTHERRMOTE site.

So I leave it at its default and use CONNECT TO, and I also, apparently, have to create the package and then distribute it. Apparently it only creates a package if necessary via the CRTSQLRPGi command, but the package needs to be on the remote server at runtime, so CRTSQLPKG is necesssary.

At least that what I think, and I don't know what I'm doing.