Deal of the Day

Saturday 6 August 2011

Special Registers


Hi Guys,

This time, I know I am back with long interval....
I would like to thank Issak Brender to provide me examples on Special Register....

A special register is a storage area that is defined for an application process by the database manager and is used to store information that can be referenced in SQL statements. A reference to a special register is a reference to a value provided by the current server


Not long time ago IBM has introduced following files
·         One record dummy file SYSIBM/SYSDUMMY1 and
·         One row dummy table QSYS2/QSQPTABL

These files permit to obtain data from any SQL register within a one-row SELECT answer like DUAL table in Oracle. This file/table is used only as a ‘frame’ and data is irrelevant.

A special register contains information that is stored on a system and can be referenced in SQL statements. They can be extracted directly or as additional field(s) to selected row of data.
Most commonly used registers are: CURRENT DATE (CURRENT_DATE), CURRENT TIME (CURRENT_TIME), CURRENT TIMESTAMP (CURRENT_TIMESTAMP), CURRENT TIMEZONE (CURRENT_TIMEZONE), LOCALTIME, USER, CURRENT SERVER.

select user, current server, current_date, current time from SYSIBM/SYSDUMMY1
select id, ord#, cust#, date(current date) as ord_dat from some_table
select (LOCALTIME) - (CURRENT TIMEZONE) from QSQPTABL
select EXTRACT(MONTH FROM CURRENT DATE) from SYSIBM/SYSDUMMY1

A little bit more about mentioned dummy file/table. Some might ask how and where else they can be used.
Quick calculations:

select dec(sqrt(1234.66), 5,3) from QSQPTABL

result 35.137


select dec(power(4.1,4), 7,3) from SYSIBM/SYSDUMMY1

result 282.576

It is very handy to test syntax for complex statements on constants before running your query on whole database:
E.g. convert social security number from numeric to character with dashes:

with ssec as (select (char(123458706)) as nbr from SYSIBM/SYSDUMMY1)
select INSERT (INSERT(nbr, 4, 0, '-' ), 7, 0, '-' ) as ssn FROM ssec

result 123-45-8706

There are plenty of ways to manipulate dates:

Select DATE(NEXT_DAY('07/30/2011', 'WEDNESDAY'))

Returns a date of 08/03/11 , which is the next Wednesday after 07/30/11.


Select monthname(now()) from SYSIBM/SYSDUMMY1

Returns current month which is August (I am writing this blog on 6th August, 2011…J ).


Note. When machine and remote workstation are located in different time zones, any extracted date/time system values are applied to machine time zone, not yours!

Some scripts can be executed on V5R4 and later

For questions, just enter your questions (here) as comment.
Then I will create a new topic based on your query.
Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning

No comments:

Post a Comment