Net.Data provides relational database language environments to help you access your relational data sources. The SQL statements you provide to access the relational data are executed as dynamic SQL. For more information on dynamic SQL, see your DB2 documentation.
Net.Data provides the following relational database language environments:
To use the ODBC language environment:
Verify that the location of your CLI initialization file is specified in the configuration variable DSNAOINI. To learn how to set the DSNAOINI configuration variable, see DSNAOINI: DB2 CLI Initialization File Variable.
Verify that the following configuration statement is in the initialization file, on one line.
ENVIRONMENT (DTW_ODBC) odbcdll ()
Allowed variables on the ENVIRONMENT statement: TRANSACTION_SCOPE, LOCATION
Restrictions:
The SQL language environment provides access to DB2 databases. Use this language environment for optimal performance when accessing DB2.
To use the SQL language environment:
Verify that the following configuration statement is in the initialization file, on one line.
ENVIRONMENT (DTW_SQL) dtwsql ()
Allowed variables on the ENVIRONMENT statement: TRANSACTION_SCOPE, LOCATION, DB2SSID, DB2PLAN
Important: See Setting up the SQL and ODBC Language Environments to learn how to set up the SQL language environment.
Restriction: SQL statements in the inline statement block can be up to 32 KB.
The following sections describe how to use these language environments:
When you modify the content of a database using insert, delete, or update statements, the modifications do not become persistent until the database receives a commit statement from Net.Data. If an error occurs, Net.Data sends a rollback statement to the database, reversing all modifications since the last commit.
The way in which Net.Data sends the commit and possible rollback depends on how you set TRANSACTION_SCOPE and whether you specify the commit explicitly in the macro. The values for TRANSACTION_SCOPE are MULTIPLE and SINGLE.
Specifies that Net.Data issues a commit statement after each successful SQL statement. If the SQL statement returns an error, a rollback statement is issued. Single transaction scope secures a database modification immediately; however, with this scope, it is not possible to undo a modification using a rollback statement later.
To activate this commit method, set TRANSACTION_SCOPE to SINGLE. For example:
@DTW_ASSIGN(TRANSACTION_SCOPE,"SINGLE")
Specifies that Net.Data will execute all SQL statements before a commit and possible rollback statement is issued. Net.Data sends the commit at the end of the request, and if each SQL statement is issued successfully, the commit makes all modifications in the database persistent. If any of the statements returns an error, Net.Data issues a rollback statement, which sets the database back to its original state. MULTIPLE is the default if TRANSACTION_SCOPE is not set, because issuing a commit after each transaction affects the performance of your database application and should only be done when necessary.
You can issue a commit statement at the end of any SQL statement in your macro by using the COMMIT SQL statement. By leaving TRANSACTION_SCOPE set to MULTIPLE and issuing commit statements at the end of those groups of statements that you feel qualify as a transaction, you the application developer maintain full control over the commit and rollback behavior in your application.
To issue an SQL commit statement, you can define a function that you can call in at any point in your HTML block:
%FUNCTION(DTW_SQL) user_commit() { commit %} ... %HTML { ... @user_commit() ... %}
You can store large object files (LOBs) in DB2 UDB Server for OS/390 Version 6 tables and incorporate them into your dynamic Web pages by using the Net.Data SQL language environment.
When the language environment executes an SQL SELECT statement or a stored procedure that returns a LOB, it does not assign the object to a V(n) table processing variable or a Net.Data table field. Instead, it stores the LOB in an HFS file that Net.Data creates and returns only the name of the file in the V(n) table processing variable or a Net.Data table field. In your Net.Data macro you can use the name to reference the LOB file; for example, you can create an HTML anchor element with a hypertext reference or an image element containing a URL for the file. Net.Data places the file containing the LOB in the directory specified by the HTML_PATH path statement, located in the Net.Data initialization file (db2www.ini). Write access to the LOB file is limited to the user ID associated with the Net.Data request that retrieved the LOB.
The file name for the LOB is dynamically constructed, and has the following form:
name[.extension]
Where:
Table 6. LOB extensions used in the SQL language environment
Extension | Object Type |
---|---|
.bmp | bitmap image |
.gif | graphical image format |
.jpg | joint photographic experts group (JPEG) image |
.tif | tagged image file format |
.ps | postscript |
.mid | musical instruments digital interface (midi) audio |
.aif | AIFF audio |
.avi | audio visual interleave audio |
.au | basic audio |
.ra | real audio |
.wav | windows audio visual |
portable document format | |
.rmi | midi sequence |
If the object type for the BLOB is not recognized, no extension is added to the file name.
When Net.Data returns the name of the file containing a LOB, it prefixes the file name with the string /tmplobs/ using the following syntax:
/tmplobs/name.[extension]
This prefix permits you to locate your LOB directory in a directory other than the Web server's document root directory.
To ensure that references to LOB files are correctly resolved, add the following Pass directive to your Web server's configuration file:
Pass /tmplobs/* <HTML_PATH>
<HTML_PATH> is the value specified for the HTML_PATH path statement in the Net.Data initialization file.
Planning tips:
Net.Data stores each LOB that it receives from DB2 in an HFS file in the directory specified by the HTML_PATH path statement. Because a LOB can be up to 2 gigabytes in size, these files can quickly consume a considerable amount of disk storage. Net.Data provides automatic management of LOBs based on expiration time, and a macro that allows a system administrator to manage LOBs in a more sophisticated fashion using the creation time of the LOBs. See Managing Cached Web Pages and Large Objects for additional information.
Restriction: Net.Data does not support UPDATE and INSERT SQL statements for large objects.
Example: The following application uses an MPEG audio (.mpa) file. Because the SQL language environment does not recognize this file type, an EXEC variable is used to append the .mpa extension to the file name. A user of this application must click on the file name to invoke the MPEG audio file viewer.
%DEFINE{ docroot="/usr/lpp/internet/server_root/html" myFile=%EXEC "mv $(docroot)$(filename) $(docroot)$(filename).mpa" %} %FUNCTION(DTW_SQL) queryData() { SELECT Name, IDPhoto, Voice FROM RepProfile %REPORT{ <P>Here is the information you selected:</P> %ROW{ @DTW_ASSIGN(filename, @DTW_rSUBSTR(V3, @DTW_rLASTPOS("/", V3))) $(myFile) $(V1) <IMG SRC="$(V2)"> <A HREF="$(V3).mpa">Voice sample</A><P> %} %} %} %HTML(REPORT){ @queryData() %}
If the RepProfile table contains information about Kinson Yamamoto and Merilee Lau, then the execution of the REPORT block will add the following HTML to the Web page being generated:
<P>Here is the information you selected:</P> Kinson Yamamoto <IMG SRC="/tmplobs/p2345n1.gif"> <A HREF="/tmplobs/p2345n2.mpa">Voice sample</A><P> Merilee Lau <IMG SRC="/tmplobs/p2345n3.gif"> <A HREF="/tmplobs/p2345n4.mpa">Voice sample</A><P>
The REPORT block in the previous example uses the implicit table variables V1, V2, and V3.
Access rights for LOBs:
Ensure that the user ID or user IDs under which Net.Data executes have write access to the directory specified by HTML_PATH.
A stored procedure is a compiled program stored in DB2 that can execute SQL statements. In Net.Data, stored procedures are called from Net.Data functions using a CALL statement. Stored procedure parameters are passed in from the Net.Data function parameter list. You can use stored procedures to improve performance and integrity by keeping compiled SQL statements with the database server. Net.Data supports the use of stored procedures with DB2 through the SQL and ODBC language environments.
This section describes following topics:
The syntax of the stored procedure uses the FUNCTION statement, the CALL statement, and optionally a REPORT block.
%FUNCTION (DTW_lang_env) function_name ([IN datatype arg1, INOUT datatype arg2, OUT tablename, ...]) { CALL stored_procedure [(resultsetname, ...)] [%REPORT [(resultsetname)] { %}] ... [%REPORT [(resultsetname)] { %}] [%MESSAGE %}] %}
Where:
Table 7. Stored Procedures Data Types
CHAR | FLOAT | SMALLINT |
DECIMAL | INTEGER | VARCHAR |
DOUBLE | GRAPHIC | VARGRAPHIC |
DOUBLEPRECISION |
|
|
%FUNCTION (DTW_SQL) function_name()
%FUNCTION (DTW_SQL) function_name (IN datatype arg1, INOUT datatype arg2, OUT tablename...)
CALL stored_procedure
%REPORT (resultsetname) { ... %}
Example:
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1) { CALL myproc %REPORT (mytable){ ... %ROW { ... %} ... %} %}
CALL stored_procedure (resultsetname1, resultsetname2, ...)
%REPORT(resultsetname1) { ... %}
Example:
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) arg1, OUT table1) { CALL myproc (table1, table2) %REPORT (table2) { ... %ROW { ... %} ... %} %REPORT (table1) { ... %ROW { ... %} ... %} %}
You can pass parameters to a stored procedure and you can have the stored procedure update the parameter values so that the new value is passed back to the Net.Data macro. The number and type of the parameters on the function parameter list must match the number and type defined for the stored procedure. For example, if a parameter on the parameter list defined for the stored procedure is INOUT, then the corresponding parameter on the function parameter list must be INOUT. If a parameter on the list defined for the stored procedure is of type CHAR(30), then the corresponding parameter on the function parameter list must also be CHAR(30).
Example 1: Passing a parameter value to the stored procedure
%FUNCTION (DTW_SQL) mystoredproc (IN CHAR(30) valuein) { CALL myproc ...
Example 2: Returning a value from a stored procedure
%FUNCTION (DTW_SQL) mystoredproc (OUT VARCHAR(9) retvalue) { CALL myproc ...
You can return one or more result sets from a stored procedure using the SQL or ODBC language environments. The result sets can be stored in Net.Data tables for further processing within your macro or processed using a REPORT block. If a stored procedure generates multiple result sets, you must associate a name with each result set generated by the stored procedure. This is done by specifying parameters on the CALL statement. The name you specify for a result set can then be associated with a REPORT block or a Net.Data table, enabling you to determine how each result set is processed by Net.Data. You can:
See Guidelines and Restrictions for Multiple REPORT Blocks for guidelines and restrictions when using multiple report blocks.
To return a single result set and use default reporting:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure %}
For example:
%FUNCTION (DTW_SQL) mystoredproc() { CALL myproc %}
To return a single result set and specify a REPORT block:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure [(resultsetname)] %REPORT [(resultsetname)] { ... %} %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc %REPORT { ... %ROW { ... %} ... %} %}
Alternatively, the following syntax can be used:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure (resultsetname) %REPORT (resultsetname) { ... %} %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc (mytable1) %REPORT (mytable1) { ... %ROW { ... %} ... %} %}
To store a single result set in a Net.Data table for further processing:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name (OUT tablename) { CALL stored_procedure (resultsetname) %}
For example:
%DEFINE DTW_DEFAULT_REPORT = "NO" %FUNCTION (DTW_SQL) mystoredproc (OUT mytable1) { CALL myproc (mytable1) %}
Note that DTW_DEFAULT_REPORT is set to NO so that a default report is not generated for the result set.
To return multiple result sets and display them using default report formatting:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name () { CALL stored_procedure [(resultsetname1, resultsetname2, ...)] %}
Where no report block is specified.
For example:
%DEFINE DTW_DEFAULT_REPORT = "YES" %FUNCTION (DTW_SQL) mystoredproc () { CALL myproc %}
To return multiple result sets and have the result sets stored in Net.Data tables for further processing:
Use the following syntax:
%FUNCTION (DTW_SQL) function_name (OUT tablename1, tablename2, ...) { CALL stored_procedure (resultsetname1, resultsetname2, ...) %}
For example:
%DEFINE DTW_DEFAULT_REPORT = "NO" %FUNCTION (DTW_SQL) mystoredproc (OUT mytable1, mytable2) { CALL myproc (mytable1, mytable2) %}
Note that DTW_DEFAULT_REPORT is set to NO so that a default report is not generated for the result sets.
To return multiple result sets and specify REPORT blocks for display processing:
Each result set is associated with its one REPORT block. Use the following syntax:
%FUNCTION (DTW_SQL) function_name (, ...) { CALL stored_procedure (resultsetname1, resultsetname2, ...) %REPORT (tablename1) ... %ROW { ... %} ... %} %REPORT (tablename2) ... %ROW { ... %} ... %} ... %}
For example:
%FUNCTION (DTW_SQL) mystoredproc () { CALL myproc (mytable1, mytable2) %REPORT(mytable1) { ... %ROW { ... %} ... %} %REPORT(mytable2) { ... %ROW { ... %} ... %} %}
To return multiple result sets and specify different display or processing options for each result set:
You can specify different processing options for each result set using unique parameter names. For example:
%FUNCTION (DTW_SQL) mystoredproc (OUT mytable2) { CALL myproc (mytable1, mytable2, mytable3) %REPORT(mytable1) ... %ROW { ... %} ... %} %}The result set mytable1 is processed by the corresponding REPORT block and is displayed as specified by the macro writer. The result set mytable2 is stored in the Net.Data table mytable2 and can now be used for further processing, such as being passed to another function. The result set mytable3 is displayed using Net.Data's default report format because no REPORT block was specified for it.
The following example shows how you can call the relational database language environments from your macros:
The following example shows a macro with a DTW_SQL function definition that calls an SQL stored procedure. For the ODBC language environment, substitute DTW_ODBC for DTW_SQL where it appears. It has three parameters of different data types. The DTW_SQL language environment passes each parameter to the stored procedure in accordance with the data type of the parameter. When the stored procedure completes processing, output parameters are returned and Net.Data updates the variables accordingly.
%{*********************************************************** DEFINE BLOCK ************************************************************%} %DEFINE { MACRO_NAME = "TEST ALL TYPES" DTW_HTML_TABLE = "YES" Procedure = "TESTTYPE" parm1 = "1" %{SMALLINT %} parm2 = "11" %{INT %} parm3 = "1.1" %{DECIMAL (2,1) %} %} %FUNCTION(DTW_SQL) myProc (INOUT SMALLINT parm1, INOUT INT parm2, INOUT DECIMAL(2,1) parm3){ CALL $(Procedure) %} %HTML(REPORT) { <HEAD> <TITLE>Net.Data : SQL Stored Procedure: Example '$(MACRO_NAME)'. </TITLE> </HEAD> <BODY BGCOLOR="#BBFFFF" TEXT="#000000" LINK="#000000"> <p><p> Calling the function to create the stored procedure. <p><p> @CRTPROC() <hr> <h2> Values of the INOUT parameters prior to calling the stored procedure:<p> </h2> <b>parm1 (SMALLINT)</b><br> $(parm1)<p> <b>parm2 (INT)</b><br> $(parm2)<p> <b>parm3 (DECIMAL)</b><br> $(parm3)<p> <p> <hr> <h2> Calling the function that executes the stored procedure. </h2> <p><p> @myProc(parm1,parm2,parm3) <hr> <h2> Values of the INOUT parameters after calling the stored procedure:<p> </h2> <b>parm1 (SMALLINT)</b><br> $(parm1)<p> <b>parm2 (INT)</b><br> $(parm2)<p> <b>parm3 (DECIMAL)</b><br> $(parm3)<p> </body> %}