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 6^th 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