Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL PKG and CRTSQLRPGI
has no ratings.
Published: 01 Feb 2012
Revised: 23 Jan 2013 - 1520 days ago
Last viewed on: 22 Mar 2017 (4733 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

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?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: SQL PKG and CRTSQLRPGI
Posted: 5 years 1 months 19 days 1 hours 3 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
Comment on: SQL PKG and CRTSQLRPGI
Posted: 5 years 1 months 12 days 14 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:

  1. Run CRTSQLPKG to create an SQL package. (Using SQLPKG on the CRTSQLRPGI command doesn't do it)
  2. 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: 5 years 1 months 11 days 21 hours 49 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
Comment on: SQL PKG and CRTSQLRPGI
Posted: 5 years 1 months 11 days 21 hours 22 minutes ago

Read that manual. Some vague stuff in there. Smile

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. Cool