Data Directions: I'll Have Two SQL Packages to Go Please!
OK, so you have SQL on your system, you can use the command STRSQL (that's the thing that will start it for you interactively) and you can use the command DELETE CUSTMAST and watch all your customer records disappear. By the way, don't really use this, it will in fact delete all your customer records if your file is CUSTMAST.
There really is some good stuff as part of SQL if you understand it. It can be used interactively, in batch, and embedded in RPG or COBOL programs. It can add records (though not very easily), it can change data, which can be very helpful, and it can display, select and summarize data--which is great stuff.
But how much do we really understand SQL? If you are from the AS/400, only what we have learned from the past few years. If you come from another SQL based database, then maybe you know more, but are trying to figure out just how the AS/400 implements it. Standard or not, not all SQL's are the same.
So what is the difference between SQL and native database interfaces (you know, CHAIN, SETLL, etc.)? The main difference is that they are different--don't you love it? SQL is a different animal than native, but if you understand it, you can really take advantage of it. I am not talking just about syntax differences here, as with any other language you can learn the syntax. It's how it works under the covers that I am talking about.
One of the first things to understand (which took me a couple of years to figure out), is the concept of packages. SQL defines databases as packages. These are kind of like libraries where all the data about the data is kept.
The first thing to remember about SQL and the AS/400 is to be on V4R3 or later. This will cure a lot of ills before you get started. There were a number of enhancements for SQL in this release, one of the biggest was the size of the SQL package. This is where the bulk of the definitions for all statements used by SQL and ODBC are stored. If you understand the SQL package, it can really save a lot of time and solve some performance issues.
One of the things I could never understand was just what an SQL package was. For some reason it is just one of those things that I have trouble translating from the AS/400 database world to the SQL world. Where do stored procedures go? When you create a trigger, how does it know what to do? (Yes, I know you can see it in DSPFD.) When using ODBC, how does the PC or other platform know how to see the data on the AS/400 in my "normal" library? And most of all, how do they relate to those check boxes in Client Access. If you have ever configured Client Access for ODBC support, there are all kinds of options that never seem to do anything (except when something goes wrong and you have to reload everything--I love PC's!).
Well, I did some research about these options for Client Access and found out some things that suddenly made sense. First off, there is no magic to the SQL package mechanism. When setting up Client Access there is an extended dynamic check box. This tells the system to use the data in the SQL package as well as other SQL API's that are used by the system, such as DRDA. This is one of the magical places where the system stores the information used to calculate which index to use in SELECTs (OK, that part is magic). This greatly improves performance since the system does not have to calculate everything dynamically.
You can use some commands to learn more about what is in an SQL package:
The PRTSQLINF command can be used to print a formatted report about the access plan that the system would use to access data. This is very helpful in determining if the system needs to build a temporary index when trying to select data with WHERE and ORDER BY clauses.
The WRKOBJ OBJ(*ALL/*ALL) OBJTYPE(*SQLPKG) command will give you a list of the packages defined on your system. This is nice to know when determining just what SQL or ODBC is looking at when using them.
You can even delete a package: DLTSQLPKG. It just gives me a nice feeling to know that I can delete something if I have to. SQL Packages store definition information - or Meta data, not user data, so prior to V4R3, IBM limited them to 16 Meg in size. You would never really know about this until all of a sudden the performance of ODBC calls or SQL SELECTs started to slow down. It may be that the Meta data stored in the package has been filled up. This is where you may want to delete the package and let the system build another one automatically. From V4R3 on, you do not need to worry about this, but there may be junk that is no longer needed and may increase the time it takes to analyze SQL statements.
So that funny little thing that shows up every once in a while--the SQL package, is important, and you may want to at least take a look at it. Tuning is one of those things that we can never learn enough about and one that is always adding data to help us and frequently confuse us.