Db2 for i comes with a host of built-in global variables and special registers that provide us with some of the basic information about the environment of the IBM i we are using.
When I am using ACS's Run SQL Scripts it is useful to be able to quickly check these values. Or if I am doing a presentation I need to be able to show which IBM i partition I am using to help people to understand what I am doing.
If I wanted to see which partition I am using I could use the following SQL statement:
|
I am sure more of you are familiar using the SYSDUMMY1 table in the library SYSIBM to do this kind of thing.
I am all for shortcuts and making things easier for myself. I need to give credit to Paul Tuohy for showing this particular shortcut.
Rather than using the Select statement I can use the VALUES statement in its place. By using the VALUES I do not have to define a file. If I want to display which database server my Run SQL Scripts session is attached to I could just use the following:
|
Having had a chance to play with this I have some interesting things you need to be aware of.
You can list more than one built-in function and special register:
|
The returned values are shown in one column.
Notice how I had to use the CHAR function with the date, time, and timestamp. I have found that:
- If the only special registers chosen are for dates, times, and timestamps the data's type does not need to be changed
- If the results contain a mixture of data types, such as character, dates, times, and timestamps, not all of the results will be displayed. Some will return null
By converting those results to character prevents this issues.
A few more examples:
|
These show how I can get day and month name from the date, and some other information from the date too. Again I had to convert the numeric results to character for all of the results to be shown.
This article was written for IBM i 7.4, and should work for some earlier releases too.
In addition, if one wants the results in different columns instead, include the values in ().
ReplyDeleteEx: values (current_server, current_time);
This stuff works fine in green on black interactive SQL. On V7R1M0 :)
ReplyDeleteGood morning Simon, I can't see the code. I am using Chrome. Thanks. Regards. Carlos
ReplyDeleteMost of the code is in image files inserted in the post.
DeleteAre those what you cannot see.
(I use Chrome too, IMHO it is the best brwoser)
Most of these special registers can be viewed in the Environment tab which is next to the Messages tab and if not visible can be seen by checking it from the View menu.
ReplyDelete-Matt
Hi, How can I store this current_server value into a variable for later use in my sql script?
ReplyDeleteUse a Global Variable
DeleteThanks Simon.
DeleteI was actually able to use local(I guess) variables in my script.
Probably not the best most clean way but it does exactly what I want.
Is there also a way to PRINT a variable on the output screen just to verify the value when you want a quick check of your sql script?
I am not sure what you mean.
DeleteDo you mean as the program runs it displays the returned data from the SQL statement? Isn't that what debug and the DSPLY op code in RPG is for?
If not you can write to the job log using LPRINTF.