For many releases of IBM i we have been able to put keyword in the H-spec/Control options that are the same as the parameters in RPG's compile options, see here. The same is also available in SQL for RPG objects with embedded SQL, SQLRPGLE.
I have given some examples in the past of using the SET OPTION statement in SQL to turn off commitment control. There are a multitude of other options that mirror options in the SQLRPGLE compile commands. I am not going to list all of the options in this post as IBM provides them all on their web site, there is a link at the bottom of this article to the page. I am only going to show what I think are the most useful.
Below are a few of them I added to a program I created:
exec sql SET OPTION COMMIT = *NONE,
NAMING = *SQL,
CLOSQLCSR = *ENDMOD,
DATFMT = *USA,
TIMFMT = *USA ;
|
Notice how each one is separated by a comma (,).
- COMMIT = *NONE - Turn off commitment control
- NAMING = *SQL - The SQL naming conventions will be used
- CLOSQLCSR = *ENDMOD - SQL cursor is closed when the module completes
- DATFMT = *USA - The format of the date used for output and on input to determine if the date is valid
- TIMFMT = *USA - The format of the time
There is an additional option that can only be used in SQL functions, procedures and triggers: DBGVIEW. If you add this to a program it will not compile giving you the following message, which in my opinion tells you that DBGVIEW is valid.
SQL0104 30 10 Position 30 Token DBGVIEW was not valid. Valid tokens:
COMMIT NAMING DECMPT DATFMT TIMFMT DATSEP TIMSEP SRTSEQ
LANGID DFTRDBCOL RDBCNNMTH ALWBLK ALWCPYDTA DLYPRP
DYNUSRPRF USRPRF CLOSQLCSR OPTLOB DYNDFTCOL SQLPATH
SQLCURRULE COMPILEOPT DECRESULT CNULRQD CNULIGN OUTPUT
DBGVIEW TGTRLS EVENTF.
|
I have found that I need to wary of differing date formats in the H-spec/Control options and in the SQL Set options. For example:
01 ctl-opt datfmt(*jul) ;
02 dcl-s wkDate date ;
03 exec sql SET OPTION DATFMT = *USA ;
04 exec sql SET :wkDate = CURDATE() ;
05 *inlr = *on ;
|
In the Control options, line 1, I have set the default Date format to Julian (YYDDD), DATFMT(*JUL).
I have defined the variable wkDate as a date variable, but without a date format keyword, therefore, it will use the format specified in the Control options.
In the Set options, line 3, I have chosen the Date format to be in USA format (MMDDYYYY), DATFMT = *USA.
On line 4 I move, in SQL, the Current date into the variable wkDate.
I added a debug break point at line 5. When I display the value of wkDate I get the date in Julian format: WKDATE = '15/201'
I recommend that you consider adding the SQL Set options to all of your SQLRPGLE programs. You might want to put it in its own source file and then use /COPY to include it into your other source members.
..... *. 1 ...+... 2 ...+... 3 ...+... 4
/copy mylib/mysrc,sqlsetopts
|
I know that /COPY and /INCLUDE are suppose to function the same way, but I could not get my test SQLRPGLE program to compile with the /INCOUDE, when it would with the /COPY. I also had to place the slash (/) in the seventh position of the source member, as I had to do in fixed format RPG . In free format RPG members, not SQLRPGLE, you can place the slash in any place on the line greater than the seventh position.
September 11, 2015
Make sure to read the comments posted by Birgitta Hauser here.
You can learn more about the Set options available on the IBM web site here.
This article was written for IBM i 7.2, and should work for earlier releases too.
I like to allow the embedded SQL statements to also run with adopted authority if the *PGM or *SRVPGM does.
ReplyDeleteUSRPRF = *OWNER,
DYNUSRPRF = *OWNER ;
Also, sometimes you want an ODP to be reused for improved performance on subsequent SELECTs (3rd run).
CLOSQLCSR = *ENDACTGRP
These are my old notes:
H* * * * ODP Reuse * * *
H* For embedded static SQL, DB2 Universal Database for iSeries
H* only reuses ODPs opened by the same statement *number*.
H*
H* Use CLOSQLCSR = *ENDACTGRP or *ENDJOB to keep ODP alive.
H*
H* Try keep SQL string a constant length.
H*
H* Use ? parameter markers in SQL string.
H*
H* If eligible to be reused, these message appear in job log:
H* 1st time SQL is run: "ODP Created", "ODP Deleted".
H* 2nd time SQL is run: "ODP Created", "ODP Not Deleted".
H* 3rd time SQL is run: "ODP Reused".
H*
H* The current SQL statement must be very similar to last ODP.
H* If number of rows to fetch changes "a lot", ODP is rebuilt.
Chris Ringer
Just a few comments about the options you set (Part 1):
ReplyDelete1. COMMIT --> Normally SQL expects inserts / updates or deletes are executed under Commitment control!
2. CLOSQLCSR --> When specifiying *ENDMOD the ODPs are always closed as soon as the module is left. That means ODPs are not reusable, in this way each time the module (better the SQL statements located within the modules) is calle, the optimizer must perform a FULL OPEN, i.e. the SQL statement in analyzed, rewritten, the access path are estimated, the access plan built (or validated), and the ODP is built (the temparary objects and there dependencies desrcibed within the access plan are built). Open the ODP is the most time consuming process when executing a query. That's why SQL tries to keep the ODPs open as long as possible. If an ODP is reusable, i.e. already opened, only a PSEUDO OPTIMIZATION must be performed. With a PSEUDO OPTIMIZATION only the data in the temporary objecs of the ODP are updated. Which is much less time consuming than a FULL OPEN.
The default option *ENDACTGRP keeps the ODPs opened until the activation group is closed. Until the activation group exists the ODPs stay opened and can be reused. With option *ENDMOD, the ODPs are deleted, and with repeated calls FULL OPENS must be performed.
In this way do NOT change the default option for CLOSQLCSR!
3. Naming *SQL:
When using naming conventions *SQL the libarary list is not searched for identitying unquilfied specified database objects (tables, views, indexes, aliases). Instead unqualified specified database objects must be located in a single (current) schema. This SQL current schema is not identical with the current library and may or may not be in the library list.
Even worse is when using *SQL naming conventions and creating database objects, the object authrities are handled differently, i.e. *PUBLIC is set to *EXCLUDE.
As long we are working only on the IBM i, we are working with library lists whith objects located in multiple libraries that are accessed. Normally no object is qualified, i.e. very often for test szenarios the library list is modified, i.e. the test library set at the beginning of the library list.
In this way, I'd not change the Option NAMING to *SQL, as long as I work only on the IBM i.
... and for those who find it sexy, separating schema and object with a period instead of a slash ... it is possible with system naming conventions since release 7.1 TR 7.
Birgitta
Thanks for this Birgitta.
DeleteOne question regarding CLOSQLCSR - in our shop virtually everyone still uses the dftactgrp, or sets dftacgrp(*no) but goes no further. My understanding is that this will leave all ILE programs running in actgrp(QILE)?
If my understanding is correct, and we always have at least one ILE program running, this should mean that the ODPs are never cleaned up (until IPL); is there any system overhead involved in this, or is it so small that it is effectively negligible compared to the overhead of rebuilding ODPs regularly?
I have to admit that I have always set my cursors to close *endmod as it seemed like good housekeeping, and didn't fully appreciate the impact - but is there any impact in leaving them open in QILE?
Thanks in advance!
Dan
The activation group is still within the context of the job. When the job ends, the activation groups associated with the job get cleaned up. At least, that's how I understand it.
DeleteBirgitta, I wanted to ask a question regarding the CLOSQLCSR option. In our shop somebody set this value to *ENDMOD a long time ago. So, every RPG-SQL object is compiled that way. My question is, what are the risks of changing the setting and letting future compiles leverage the CLOSQLCSR = *ENDACTGRP? I can see where any cursor not properly closed could leave a record locked until the activation group ends. Aside from that, I cant think of what other problems might be exposed. I'm trying to weigh out how safe it is to make that change and let compiles change the objects little at a time as they have other changes. Is this change fairly safe or is it something where we need to isolate the members compiled with CLOSQLCSR=*ENDACTGRP and test, releasing them slowly. What are your thoughts? Thanks in advance.
DeleteJust a few comments about the options you set (Part 2):
ReplyDelete4. *DATFMT = *USA
Setting the date format to a format with a 4 digit year is very important, because the precompiler creates additional variables for each host variable that is used. For date and time fields it does not take the date or time format specified in the H or D specs, but from the DATFMT compile option.
The default value for the DATFMT compile option is *JOB, i.e. the date normally has only a 2 digit year definition.
Your program may crash when using the default option and intiailizing your 4-digit-year host variable with *LOVAL. The *LOVAL (0001-01-01 is moved to the 2-digit-year addtional SQL variable and that's it.
The date format specified in the compile option has nothing to do with the format in which format a date is displayd, this depends on the format used in your job.
A date is always stored and maintained in an internally numeric representation (scaliger no) starting with January, 1 4713 BC.
(Just try to display the hex value for the current date with SQL: Values(HEX(Current_Date));
Date Formats are only needed to make this scaliger no readable.
In this way I'd not use the date format *USA but *ISO.
5. *TIMTMT = *USA
When using the timeformat USA the seconds may go lost, because the storage for these information is replaced with AM/PM.
I'd not change the default value to *USA.
6. /Include and /Copy
The difference between /INCLUDE and /COPY is that the SQL precompiler ignores copy members included with /INCLUDE but consideres the /COPY members.
I'd always use the /INCLUDE clause, if the copy member includes information that are not important for the SQL precompiler (for example prototypes).
Birgitta
Thank you very much for your comments.
Deletewe use our change management system to set the defaults for CRTSQLRPGI when compiling
ReplyDeleteThis sure helps as we include more embedded SQL in our applications.
ReplyDeleteYes, and it will ensure that when other compile the code the final object will act the same way I intended. Which does not happen if I do not use the SQL OPTIONS & someone just uses PDM & 14s the source.
DeleteYou mentioned the DBGVIEW option is only valid in SQL stored procedures, SQL functions, and triggers. Although these articles have an RPG focus, it's worth mentioning that these SQL routines can be very powerful. A couple of places in the Infocenter/KnowledgeCenter have more detail that may be useful:
ReplyDeleteDatabase->Programming->SQL programming->Routines
Database->Reference->SQL control statements
Note that SQL control statements include CALL, CASE, FOR, IF, LOOP, and WHILE, among others. The compound-statement allows one to declare variables, cursors, and condition handlers. The assignment-statement allows one to SET the value of a variable, perhaps similar to EVAL in RPG.