SQLRPGLE User Guide
Blogs

A Programmer’s Guide To: Special Registers in SQLRPGLE

By PIO TEAM on July 30, 2025

Transform your SQLRPGLE coding experience with special registers—because the right tools make all the difference.

So, let us….,

Dive into the world of SQLRPGLE special registers and see how they can transform your approach to database programming.

Introduction

Special registers in SQLRPGLE (SQL within RPGLE) are unique, system-defined variables that provide metadata and system information about the state of the SQL environment. The use of these registers can benefit operations such as tracking user sessions, auditing database changes, and enhancing application logic. In SQLRPGLE, these special registers can be seamlessly integrated into your code, providing valuable context and control over your SQL operations. 

An SQLRPGLE program can define and use special registers as follows:

1. Now()

  • A timestamp can be retrieved using the NOW() special register.
  • Below is the declaration of TSVAR, a program variable of type TIMESTAMP.
  • The below query can be used to fetch the current timestamp in the variable TSVAR.
  • In this case, the output will be as follows:

2. Current Time

  • The CURRENT TIME special register can be used to fetch the current time.
  • TIMEVAR is a program variable of TIME type, and it is declared as below.
  • The below query can be used to fetch the current time in variable TIMEVAR.
  • In this case, the output will be as follows:

3. Current Date

  • The CURRENT DATE special register can be used to fetch the current time.
  • DATEVAR is a program variable of DATE type, and it is declared as below.
  • The below query can be used to fetch the current date in the variable DATEVAR.
  • In this case, the output will be as follows:

4. Current Timestamp

  • The CURRENT TIMESTAMP special register can be used to fetch the current timestamp.
  • TSVAR is a program variable of TIMESTAMP type, and it is declared as below.
  • The below query can be used to fetch the current timestamp in the variable TIMESTAMP.
  • In this case, the output will be as follows:

5. Current User

  • The CURRENT USER special register can be used to fetch the current user.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch the current user in the variable CHARVAR.
  • In this case, the output will be as follows:

6. Current Timezone

  • The CURRENT TIMEZONE special register can be used to fetch the difference between UTC and local time at the current server.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch the difference between UTC and local time at the current server in variable CHARVAR.
  • In this case, the output will be as follows:

7. User

  • The USER special register can be used to fetch the run-time authorization ID at the current server.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch current the run-time authorization ID at the current server in variable CHARVAR.
  • In this case, the output will be as follows:

8. Current Schema

  • The CURRENT SCHEMA special register can be used to fetch the schema name used to qualify unqualified database object references.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch current the schema name in the variable CHARVAR.
  • In this case, the output will be as follows:

9. Current Server

  • The CURRENT SERVER special register can be used to fetch the current application server name.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch the current server’s name in the variable CHARVAR.
  • The output will be as follows:

10. Session_User

  • The SESSION_USER special register can be used to fetch the run-time authorization ID at the current server and this special register is a synonym for the USER special register.
  • CHARVAR is a program variable of CHAR type, and it is declared as below.
  • The below query can be used to fetch current the session user name in the variable CHARVAR.
  • The output will be as follows:

Benefits of Using Special Registers

  • Convenience: Simplifies code by reducing the need for additional queries.
  • Performance: Enhances performance by leveraging built-in variables instead of querying system tables.
  • Accuracy: Ensures that the data is accurate and consistent with the current context.
  • Security: Helps in auditing and tracking user actions without extensive code.

Disadvantages

  • Special registers reflect the state of the database or system at a specific time. If the state changes unexpectedly (e.g., time zone change, system settings), it can affect the behavior of your program, leading to unexpected results or errors.
  • Although special registers provide useful information (like current timestamps or user IDs), they may not satisfy all custom requirements or business logic requirements.

Conclusion

The SQLRPGLE special registers provide a powerful way to access system and session-related information. Adding these registers to your programs enhances their functionality, improves performance, and improves security and auditing. Whether you are logging user actions, setting default values, or managing session-specific data, special registers are essential in database programming.

SHARE: