Reference

Net.Data Language Environment Variables

Use these variables with functions to help you customize the way FUNCTION blocks are processed by language environments. Each variable has a default value. You can override the default value by assigning a new value to the variable.

• DATABASE
• DB_CASE
• DB2PLAN
• DB2SSID
• DTW_APPLET_ALTTEXT
• DTW_EDIT_CODES
• DTW_PAD_PGM_PARMS
• DTW_SAVE_TABLE_IN
• DTW_SET_TOTAL_ROWS
• LOCATION
• LOGIN
• NULL_RPT_FIELD
• PASSWORD
• SHOWSQL
• SQL_STATE
• TRANSACTION_SCOPE

DATABASE

Purpose

Specifies the database or ODBC data source to access when calling a database function. This variable can be changed multiple times within a macro to access multiple databases or ODBC data sources.

OS/400 operating system: This variable is optional. Net.Data, by default, specifies DATABASE="*LOCAL"; the DTW_SQL language environment uses the local relational database directory entry.

Windows NT, OS/2, and UNIX operating systems: Define this variable before calling any database function, except when using the DTW_ORA (Oracle) language environment. Additionally, you must use Live Connection when accessing multiple databases from the same HTML block and through the same language environment.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DATABASE="dbname"

Table 7. DATABASE Values

Examples

Example 1: Specifies to connect to the CELDIAL database for any SQL operations

%DEFINE DATABASE="CELDIAL"
 
%FUNCTION (DTW_SQL) getRpt() {
SELECT * FROM customer
%}
 
%HTML (report) {
%INCLUDE "rpthead.htm"
@getRpt()
%INCLUDE "rptfoot.htm"
%}

The database CELDIAL is accessed when the function getRpt is called.

Example 2: Overrides previous DATABASE definitions with DTW_ASSIGN

%DEFINE DATABASE="DB2C1"
...
%HTML(monthRpt){
@DTW_ASSIGN(DATABASE, "DB2D1")
%INCLUDE "rpthead.htm"
@getRpt()
%INCLUDE "rptfoot.htm"
%}

The HTML block queries the database DB2D1, regardless of what the previous value for DATABASE was.

DB_CASE

Purpose

Specifies which case to use for SQL commands and converts all characters to either upper or lower case. If this variable is not defined, the default action is to not convert the SQL command characters.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DB_CASE="UPPER"|"LOWER"

Table 8. DB_CASE Values

Examples

Example 1: Specifies upper case for all SQL commands

%DEFINE DB_CASE="UPPER"

DB2PLAN

Purpose

Allocates a plan for a connection to a local DB2 subsystem. The variable specifies the name of a plan for the Net.Data SQL language environment at the local DB2 subsystem that Net.Data will access.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Requirement: For the value of this variable in the macro to take effect, it must be listed on the ENVIRONMENT statement for the SQL language environment.

Values

 DB2PLAN="plan_name"

Table 9. DB2PLAN Values

Examples

Example 1: Specifies the plan in the DEFINE statement

%DEFINE DB2PLAN="DTWGAV22"

DB2SSID

Purpose

Establishes a connection to a local DB2 subsystem. The variable specifies the subsystem ID of the local DB2 subsystem that Net.Data will access. Only one local database connection is allowed for each macro.

Requirement: For the value of this variable in the macro to take effect, it must be listed on the ENVIRONMENT statement for the SQL language environment.

Values

 DB2PLAN="subsytem_id"

Table 10. DB2SSID Values

Examples

Example 1: Specifies a subsystem ID in the DEFINE statement

%DEFINE DB2SSID="DBNC"

DTW_APPLET_ALTTEXT

Purpose

Displays HTML tags and text to browsers that do not recognize the APPLET tag and is used with the the Applet language environment.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DTW_APPLET_ALTTEXT="HTML_text_and_tags"

Table 11. DTW_APPLET_ALTTEXT Values

Examples

Example 1: Alternate text that indicates a Web browser restriction

%DEFINE DTW_APPLET_ALTTEXT="<P>Sorry, your browser is not java-enabled."

DTW_EDIT_CODES

Purpose

Converts NUMERIC, DECIMAL, INTEGER and SMALLINT data types that are returned as a result of an SQL operation for the DTW_SQL language environment. The variable DTW_EDIT_CODES is a string of characters that correspond to the resulting columns of the table that DTW_SQL LE will build; for example, the fifth character in DTW_EDIT_CODES will be applied to the fifth column of the result set if that column is one of the supported types. This single character can be any of the supported system supplied edit codes that are defined in Data Description Specification Reference.

For example, a DECIMAL(6,0) field would normally be displayed as the character string '112698'. By specifying an edit code of 'Y' for that column in the variable DTW_EDIT_CODES, the corresponding column in the resulting table is displayed as a character string that represents the date of '11/26/98'.

Tip: Applying a user-supplied edit code to a column that results in a character string with non-numeric characters (such as commas or currency symbols) can cause syntax errors if the character string is sent back to the server for subsequent processing within a Net.Data macro. For example, the non-numeric column value might be used for numeric comparisons in subsequent DTW_SQL functions calls, causing syntax errors.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DTW_EDIT_CODES="edit_code"

Table 12. DTW_EDIT_CODES Values

Examples

Example 1:

@DTW_ASSIGN(DTW_EDIT_CODES "JJLJJ*******Y")

DTW_PAD_PGM_PARMS

Purpose

Indicates to a language environment whether character parameters (data type of CHAR or CHARACTER) are to be padded with blanks when they are being passed to a program or stored procedure.

For IN or INOUT parameters, if the length of parameter value is less than the precision that is specified, blanks are inserted to the right of the parameter value until the length of the parameter value is the same as the precision.

For OUT parameters, the parameter value is set to precision blanks.

After the call to the program or stored procedure, all trailing blanks are removed from OUT and INOUT parameter values.

Set this variable in the Net.Data initialization file to specify a value for all of your macros. You can override the value by defining it in the macro. If DTW_PAD_PGM_PARMS is not defined in the macro, it uses the value in the Net.Data initialization file.

DTW_PAD_PGM_PARMS is supported by the Direct Call and SQL language environments.

Values

DTW_PAD_PGM_PARMS="YES"|"NO"

Table 13. DTW_PAD_PGM_PARMS Values

Examples

Example 1: Pads parameters with blanks

DTW_PAD_PGM_PARMS="YES"

DTW_SAVE_TABLE_IN

Purpose

Identifies a table variable that the SQL language environment uses to store table data from a query. This table can then be used later, for example, in a REXX program that analyzes table data.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DTW_SAVE_TABLE_IN="table_name_var"

Table 14. DTW_SAVE_TABLE_IN Values

Examples

Example 1: A previously-defined table variable used in a REXX call

%DEFINE theTable = %TABLE(2)
%DEFINE DTW_SAVE_TABLE_IN = "theTable"
 
%FUNCTION(DTW_SQL) doQuery() {
SELECT MODNO, COST, DESCRIP FROM EQPTABLE
WHERE TYPE='MONITOR'
%}
 
%FUNCTION(DTW_REXX) analyze_table(myTable) {
  %EXEC{ anzTbl.cmd %}
%}
 
%HTML(doTable) {
@doQuery()
@analyze_table(theTable)
%}

A REXX FUNCTION block calls the REXX program anzTbl.cmd, which uses the table variable theTable to analyze data in the table. The variable theTable was returned from a previous SQL function call.

DTW_SET_TOTAL_ROWS

Purpose

Specifies to a database language environment that the total number of rows in the result set for a query should be assigned to TOTAL_ROWS.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

OS/400, OS/2, Windows NT, and UNIX users: To pass this variable to the language environment, include it as an IN variable in the database language environment's ENVIRONMENT statement in the Net.Data initialization file. See the configuration chapter of Net.Data Administration and Programming Guide to learn more about the database language environment statement.

OS/390 users: DTW_SET_TOTAL_ROWS is implicitly passed to the database language environments when it is defined in the macro.

Performance tip: Setting DTW_SET_TOTAL_ROWS to YES affects performance because to determine the total rows, the database language environment requires that all rows be retrieved.

Values

DTW_SET_TOTAL_ROWS="YES"|"NO"

Table 15. DTW_SET_TOTAL_ROWS Values

Examples

Example 1: Defines DTW_SET_TOTAL_ROWS for using TOTAL_ROWS

%DEFINE DTW_SET_TOTAL_ROWS="YES"
 
...
 
%FUNCTION (DTW_SQL) myfunc() {
select * from MyTable
%report {
...
%row
...
%}
<P>Your query is limited to $(TOTAL_ROWS) rows.  The query returned too many rows.
%}
%}

LOCATION

Purpose

Establishes a connection to a remote database server. The variable specifies the name by which the remote server is known to the local DB2 subsystem. The value of LOCATION must be defined in the SYSIBM.SYSLOCATIONS table of the Communications Database (CDB). If this variable is not defined within a macro, any SQL requests made by the macro are executed at the local DB2 subsystem.

Requirement: For the value of this variable in the macro to take effect, it must be listed on the ENVIRONMENT statement for the SQL language environment.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

LOCATION="remote_dbase_name"

Table 16. LOCATION Values

Examples

Example 1: Defines the remote database location in the DEFINE statement

%DEFINE LOCATION="QMFDJ00"

LOGIN

Purpose

Provides access to protected data by passing a user ID to the database language environment. Use this variable with PASSWORD to incorporate the security algorithms of DB2.

OS/400 Users: OS/400 ignores both LOGIN and PASSWORD if the DATABASE variable is not defined or if it is set to a value of "*LOCAL". Database access is routed through the user profile under which Net.Data is running.

Security tip: While you can code this value in the Net.Data macro, it is preferable to have the application user enter user IDs in an HTML form. Additionally, using the default value of the Web server ID provides a level of access that might not meet your security needs.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

LOGIN="database_user_id"

Table 17. LOGIN Values

Examples

Example 1: Restricting access to the user ID, DB2USER

%DEFINE LOGIN="DB2USER"

Example 2: Using an HTML form input line

USERID&#58;  <INPUT TYPE="text" NAME="LOGIN" SIZE=6>

This example shows a line you can include as part of an HTML form for application users to enter their user IDs.

NULL_RPT_FIELD

Purpose

Specifies a string the user can provide to the DTW_SQL language environment to represent NULL values that are returned in an SQL result set.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

NULL_RPT_FIELD="null_char"

Table 18. NULL_RPT_FIELD Values

Examples

Example 1: Specifies a string representing NULL values in the SQL language environment

%DEFINE NULL_RPT_FIELD = "++++"

PASSWORD

Purpose

Provides access to protected data by passing a password to the database language environment. Use this variable with LOGIN to incorporate the security algorithms of DB2.

OS/400 Users: OS/400 ignores both LOGIN and PASSWORD if the DATABASE variable is not defined or if it is set to a value of "*LOCAL". Database access is routed through the user profile under which Net.Data is running.

Security tip: While you can code this value in the Net.Data macro, it is preferable to have application users enter passwords in an HTML form.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

PASSWORD="password"

Table 19. PASSWORD Values

Examples

Example 1: Restricting access to application users with the password NETDATA

%DEFINE PASSWORD="NETDATA"

Example 2: HTML form input line

PASSWORD&#58; <INPUT TYPE="password" NAME="PASSWORD" SIZE=8>

This example shows a line you can include as part of an HTML form for application users to input their own passwords.

SHOWSQL

Purpose

Hides or displays the SQL of the query used on the Web browser. Displaying the SQL during testing is especially helpful when you are debugging your Net.Data macros. SHOWSQL can only be used if DTW_SHOWSQL is set to YES in the Net.Data configuration file. For more information about the DTW_SHOWSQL configuration variable, see the configuration chapter in Net.Data Administration and Programming Guide for your operating system.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

SHOWSQL="YES"|"NO"

Table 20. SHOW_SQL Values

Examples

Example 1: Displays all SQL queries

In the configuration file:

DTW_SHOWSQL YES
 

In the macro:

%DEFINE SHOWSQL="YES"
 

Example 2: Specifying whether to display SQL using HTML form input.

In the configuration file:

DTW_SHOWSQL YES
 

In the macro:

SHOWSQL: <INPUT TYPE="radio" NAME="SHOWSQL" VALUE="YES"> Yes
         <INPUT TYPE="radio" NAME="SHOWSQL" VALUE="" CHECKED> No
 

SQL_STATE

Purpose

Accesses or displays the SQL state value returned from the database.

This variable is a predefined variable and its value cannot be modified. Use the variable as a variable reference.

Examples

Example 1: Displays the SQL state in the REPORT block

%FUNCTION (DTW_SQL) val1() {
 select * from customer
%REPORT {
 ...
%ROW {
 ...
%}
 SQLSTATE=$(SQL_STATE)
%}
 

TRANSACTION_SCOPE

Purpose

Specifies the transaction scope for SQL commands, determining whether Net.Data issues a COMMIT after each SQL command or after all SQL commands in an HTML block complete successfully. When you specify that all SQL commands must complete successfully before a commit, an unsuccessful SQL command causes all previously executed SQL to the same database in that block to be rolled back.

For the TRANSACTION_SCOPE variable to take effect, include it in the ENVIRONMENT statement in the Net.Data configuration file. You can then specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Consistency considerations: On operating systems other than OS/400 and OS/390, updates to the database receiving unsuccessful responses might be rolled back while the updates to the other databases accessed in the same HTML block might be committed when all of the following conditions are true:

• TRANSACTION_SCOPE = "MULTIPLE" is specified
• Multiple databases are accessed in one HTML block (which is possible when using Live Connection)
• An unsuccessful response is returned from an SQL request

If you access multiple databases from Net.Data on OS/400 or using IBM's DataJoiner, you can achieve multiple database update coordination and consistency when updating from Net.Data.

On OS/400 and OS/390, TRANSACTION_SCOPE = "MULTIPLE" causes all IBM database updates issued from a single HTML block to be committed or rolled back together.

On operating systems other than OS/400, the REXX, Perl, and Java language environments run in their own separate operating system processes. Thus, any database updates you issue from these language environments are committed or rolled back separately from database updates issued from a Net.Data macro, regardless of the Net.Data TRANSACTION_SCOPE value.

Values

TRANSACTION_SCOPE="SINGLE"|"MULTIPLE"

Table 21. TRANSACTION_SCOPE Values

Examples

Example 1: Specifies to issue a COMMIT after each transaction

%DEFINE TRANSACTION_SCOPE="SINGLE"