The DB2 Web Service Enabler (DB2WSE) from KRENGELTECH is a program which runs on the IBM i (AS/400, iSeries, System i), which enables external applications to access data on the IBM i, by running SQL statements over tables in the IBM i database, retrieving row-level data and returning it in an XML document.
DB2WSE can be called as a web service by external applications to perform a DB2WSE 'request', which will access one or more tables (as specified in the SQL statement passed in the request) and will return the data in those rows or will insert, update or delete one or more rows in the table, depending on the SQL statement used.
A full request consists of XML which defines the SQL statement to execute (which can be a SELECT, UPDATE, DELETE or INSERT statement), a user name and password, and various options which specify the format of the response, as in the following example:
<DB2WSEREQ>A simple request consists simply of a URL in the REST format, which can be used to perform an SQL SELECT statement only, requesting all or a keyed subset of the rows in a table, as in the following example:
DB2WSE will process the request (including checking the existence of the user name, the validity of the password and the authority of the user to perform the specified operation on the specified table(s)), perform the requested action on the table and return the requested information as XML. See the SQL/XML Examples section for examples of DB2WSE requests and responses for all types of SQL statements.
Additionally, DB2WSE allows you to
DB2WSE was created on an IBM i running V5R3M0 of OS/400 but has been compiled to run at all releases from V5R1M0 onwards.
DB2WSE requires the Apache HTTP Server, which is typically installed as part of the base i5/OS Operating System:
-- 5722DG1 *BASE IBM HTTP ServerDB2WSE does not require any separately licensable components of i5/OS, nor does it require any 3rd-party software.
DB2WSE does not require that an Application Server be installed.
DB2WSE is written entirely in RPGIV, CLLE and C and does not use Java (although it can be used by applications written in Java).
Before installing DB2WSE, read the license agreement titled 'License Agreement.pdf'. By installing this software, you declare that you agree to abide by the license agreement.
To install DB2WSE, follow these steps:
Before using DB2WSE, you should read the DB2WSE Configuration Values section and (if you choose to use the DB2WSE Authority Model) set up user profiles and authorities, as described below.
If you wish to create the example PETSTORE library, you should run the commands below. This will create both the PETSTORE library and the tables in it and also populate those tables with data. These tables are used in many of the examples in this user guide.
The following will setup a new user named DEMO using the DB2WSE Authority Model (DAM) and give DEMO authority to display data from the PETSTORE tables.
Now you should be set to try the browser application shipped with DB2WSE which tests the data access functionality by putting a simple interface over the XML transactions. To use the browser application, point your browser at http://<ibmi>:<port> where <ibmi> should be replaced with the IP address or domain name of your IBM i and <port> should be replaced with the port number you specified in the INSTDB2WSE command when installing DB2WSE.
DB2WSE is controlled by a number of configuration values, which are held in the DB2WSECFG table. The configuration values are all shipped with default values, but they may be changed where required. The following are the current valid configuration values:
The CONVERT_PASSWD_CASE configuration value controls the conversion of passwords to upper-case for creation/checking purposes.
If CONVERT_PASSWD_CASE is set to *YES, any DB2WSE profiles created or changed will have the password value specified in the PASSWD parameter to the CRTDWEPRF (Create DB2WSE Profile) command converted to upper-case prior to being stored. Additionally, when a password is passed in a DB2WSE request, it will be converted to upper-case before profile checking is performed.
If CONVERT_PASSWD_CASE is set to *NO, any DB2WSE profiles created or changed will use the password value specified in the PASSWD parameter to the CRTDWEPRF command with no conversion to upper-case prior to being stored. Additionally, when a password is passed in a DB2WSE request, it will be used exactly as it was passed when DB2WSE performs profile checking.
The default value of CONVERT_PASSWD_CASE is *YES.
The DEFAULT_AUTH_LEVEL configuration value specifies the default value that should be used for newly-created DB2WSE profiles and queries. The DEFAULT_AUTH_LEVEL configuration value can take any value from 1 to 999. See the details of the CRTDWEPRF and CRTDWEQRY (Create DB2WSE Query) commands for more details on how this configuration value is used.
The default value of DEFAULT_AUTH_LEVEL is 500
The DEFAULT_PROFILE_ID configuration value specifies whether a particular DB2WSE profile should be used as the default - if no user name and password were passed in the request. It is only valid if the USE_SYSTEM_AUTHORITY configuration value is set to *NO.
If DEFAULT_PROFILE_ID has a value other than 0 (zero) and a user name and password are not passed in a DB2WSE request, the specified profile will be used when checking the authority to the specified table.
Note that you should not set DEFAULT_PROFILE_ID to a value other than 0 unless you are certain that the specified profile has only limited authority.
The default value of DEFAULT_PROFILE_ID is 0.
The MAX_SELECT_ROWS configuration value specifies the maximum number of rows that can be returned as the result of a SELECT request.
If a SELECT statement is specified which does not include a WHERE clause and which does not specify the FORROWS attribute to the SELECT statement (in other words, if all the rows in the table are requested), DB2WSE will immediately return an error if the number of rows in the table exceeds this value.
If a SELECT statement is specified which includes either a WHERE clause or the FORROWS attribute to the SELECT statement, DB2WSE will return up to this number of rows. No error will be signaled.
The default value of MAX_SELECT_ROWS is 2500.
The USE_SYSTEM_AUTHORITY configuration value controls the 'authority model' used by DB2WSE.
If USE_SYSTEM_AUTHORITY is *YES, DB2WSE will use the System Authority Model. If USE_SYSTEM_AUTHORITY is *NO, DB2WSE will use the DB2WSE Authority Model. See the DB2WSE Authority Models chapter for details of the two different authority models.
The default value of USE_SYSTEM_AUTHORITY is *NO.
DB2WSE can use either of two separate authority 'models' to determine the authority of a user to access a table. They are known as the System Authority Model (SAM) and the DB2WSE Authority Model (DAM). The authority model which is used is controlled by the USE_SYSTEM_AUTHORITY configuration value in table DB2WSE/DB2WSECFG.
If DB2WSE is using SAM, a DB2WSE request must include a valid IBM i user name and password. DB2WSE will use system API's to validate the user name and password and check the authority of the user profile to the table. DB2WSE will run under that user profile while the request is being processed. DB2WSE profiles and authorities are not used for the request.
If DB2WSE is using DAM, a DB2WSE request must include a user name and password that have previously been created with the CRTDWEPRF command. The authority of the profile to access the table is controlled by the authority previously created for the profile using the CRTDWEAUT command. DB2WSE will run under the QSYS user profile. The user name and password do not have to be a valid IBM i user profile. If the DEFAULT_PROFILE_ID configuration value is set to a value other than 0, then no user name/password need to be passed to DB2WSE, but the default profile will be used when checking table authorities.
Using SAM is easier to set up than using DAM, since there is no need to create DB2WSE profiles and authorities. However, each request will include a clear text user profile and password, so should not be saved for repeated use. Additionally, unless HTTPS is being used to communicate between the client and the IBM i, the user profile and password will be sent unencrypted from the client to the IBM i. SAM works well in an environment where DB2WSE will be used to make ad-hoc queries and where the users already have specific authority created for them on the IBM i.
By contrast, if DAM is used, a finer control can be gained over the specific types of table access (read, update, write and delete) which are allowed for each user. Additionally, DB2WSE profiles can be created which have limited access to only a few tables, allowing them to be stored with the request and limiting the problems that could occur if they are discovered by unauthorized persons. If the DEFAULT_PROFILE_ID value is also used, then queries can be created which do not need to pass a user name and password at all.
DB2WSE includes a number of commands which allow you to install, configure and report on DB2WSE. It also includes two licensing commands, which are used by all KRENGELTECH products.
The Create DB2WSE Profile (CRTDWEPRF) command allows you to create a new profile (or update an existing profile) for use by DB2WSE. These profiles are only needed if DB2WSE is running under the DB2WSE Authority Model (DAM).
If an existing profile is specified for the USRNAM parameter, it will be updated with the PASSWD and PRFSTS values. Otherwise, a new profile will be created.
CRTDWEPRF has the following parameters:
Specifies the user name for the profile. IBM i naming rules must be used for this parameter.
Specifies the password for the profile.
If the CONVERT_PASSWD_CASE configuration value is set to *YES, then the password will be converted to upper-case before being saved. When a password is passed in a request, it will be converted to upper-case before being checked.
If the CONVERT_PASSWD_CASE configuration value is set to *NO, the password can be mixed-case. Care must be taken that the password passed in a request exactly matches this password.
Specifies the status of the profile. Can take values of either *ACTIVE or *INACTIVE
Specifies the authority level of the user profile. The authority level of a profile specifies the maximum authority level of predefined queries that can be run by the profile. AUTLVL can be specified as any value from 1 to 999. If the special value of *DFT is used, the value held in the DEFAULT_AUTH_LEVEL configuration value is used.
This will create a DB2WSE profile called DEMO with a password of DEMO. The profile will be active and will use the default authority level (as held in the DEFAULT_AUTH_LEVEL configuration value).
The Create DB2WSE Authority (CRTDWEAUT) command allows you to create an authority row which will be associated with a DB2WSE profile. These authorities are only needed if DB2WSE is running under the DB2WSE Authority Model (DAM).
A DB2WSE authority specifies the authority that the associated DB2WSE profile has to a specific table or tables. The authority can specify whether the profile can read, update, delete or write to the table.
The associated DB2WSE profile must already have been created using the CRTDWEPRF command.
Specifies the user name for the profile. IBM i naming rules must be used for this parameter.
Specifies the name of the library where the specified table (file) exists. The library does not need to exist when this command is run.
Specifies the name of the table (file) to which the profile should be granted authority. The table (file) does not need to exist when this command is run.
Specifies whether the specified user has write authority to the table (file) specified in the TBLLIB and TBLNAM parameters. This equates to the ability to run INSERT statements over the table. It can take values of either *NO or *YES
Specifies whether the specified user has delete authority to the table (file) specified in the TBLLIB and TBLNAM parameters. This equates to the ability to run DELETE statements over the table. It can take values of either *NO or *YES
Specifies whether the specified user has update authority to the table (file) specified in the TBLLIB and TBLNAM parameters. This equates to the ability to run UPDATE statements over the table. It can take values of either *NO or *YES
Specifies whether the specified user has display authority to the table (file) specified in the TBLLIB and TBLNAM parameters. This equates to the ability to run SELECT statements over the table. It can take values of either *YES or *NO
This will create a DB2WSE authority which will allow DB2WSE profile DEMO to access any of the tables in the PETSTORE library using an SQL SELECT statement. However, DEMO will not be able to insert, delete or update any rows in any tables in the PETSTORE library.
The Create DB2WSE Query (CRTDWEQRY) command allows you to create a predefined SQL query, which consists of a single SQL SELECT statement. This SQL statement can be run from a DB2WSE request by specifying the QUERYID attribute to the STATEMENT element in a full DB2WSE request or by specifying the query number instead of the library and table name in a simple REST-format DB2WSE request. When a DB2WSE query is created, it is assigned a unique number.
Specifies the authority level of the query. The authority level of a query specifies the minimum authority level of DB2WSE profiles that can run the query. AUTLVL can be specified as any value from 1 to 999. If the special value of *DFT is used, the value held in the DEFAULT_AUTH_LEVEL configuration value is used.
Specifies the SQL SELECT statement that should be run when this query is invoked.
This will create a DB2WSE query which will run the specified SELECT statement. Any DB2WSE profile with an AUTLVL greater than or equal to the *DFT level will be able to run the query. When the above statement is run, the query is created and a completion message is issue stating query identifier.
The Delete DB2WSE Profile (DLTDWEPRF) command allows you to delete an existing DB2WSE profile. Both the profile and all authority rows associated with the profile will be deleted.
Specifies the user name for the profile. IBM i naming rules must be used for this parameter.
This will delete the DEMO DB2WSE profile, and any authority rows which have been created for the DEMO DB2WSe profile.
The Install DB2WSE (INSTDB2WSE) command allows you to install DB2WSE on your IBM i.
Specifies the name of the DB2WSE library which is being installed.
Specifies the port number to use for the HTTP server instance which will be used by DB2WSE. When the HTTP server instance is started (either through the STRTCP or the STRTCPSVR command), the HTTP server instance will listen at the specified port number for client requests.
Specifies the name of the HTTP server instance which will be created.
This will initialize the DB2WSE library, and create an HTTP server instance called DB2WSE, which will listen for requests on port 8282.
This command allows you to print a list of the DB2WSE configuration, profile and authority rows. This command has no parameters.
The Apply KTI License (APYKTILIC) command allows the user to apply a license key for one or more KRENGELTECH products.
A license key for a product allows you to use that product, according to the terms of the license, until the license expiration date.
A license key consists of a 32-character string composed of valid hexadecimal characters (0-9, A-F). All 32 characters must be entered into the LICKEY field.
After executing this command, the license key is automatically validated and the list of component products to which it applies are displayed, showing the expiration date for the license.
A license key consists of valid hexadecimal characters (0-9, A-F) only.
Note: The above license key value is for example purposes only and is not a valid license key.
This command allows you to display the license code for DB2WSE, showing the expiration date.
The general format of a DB2WSE request (full format) is as follows:
<DB2WSEREQ>which would be sent in an HTML document form using the HTTP or HTTPS protocol as follows:
http[s]://{server-address}[:{port}]/db2wse/Examples of valid SQL statements are as follows:
select * from PETSTORE/ITEMDB2WSE request elements, attributes and attribute values are case-independent. SQL statements are case-independent, except for quoted strings.
Note: Unless specified otherwise, the attributes for the STATEMENT element are valid only for SELECT statements, and are ignored where an INSERT, UPDATE or DELETE statement is specified.
STATEMENT attributes and attribute values are case-independent.
For SELECT statements, specifies the first matching row to return. If not specified or specified as <=0, defaults to 1
For SELECT statements, specifies the last matching row to return. If not specified or specified as <=0, defaults to last matching row.
For SELECT statements, specifies the maximum number of matching rows to return. If not specified or specified as <=0, defaults to the value of the MAX_SELECT_ROWS configuration value.
For all statements, specifies naming type. Must be either 'SYS' (library/file) or 'SQL' (library.table). If not specified, defaults to 'SYS'.
For SELECT statements, specifies column headers. Must be either 'FLDNAM', 'SQLLABEL' or 'ANY'. If not specified, defaults to 'FLDNAM'. This attribute is ignored if RSPTYPE is specified.
For SELECT statements, specifies the response type. Must be one of the following values: 'BASIC', 'FULL', 'DATAONLY', 'COLDEFS'. If not specified, defaults to 'BASIC'. This attribute will override any value specified for the COLHDRS attribute.
See SELECT statement response format for details of the formats of the different response types, both for SELECT statements with different RSPTYPE values and also for non-SELECT statements.
If a predefined query (which was previously created with the CRTDWEQRY command) should be run, the QUERYID attribute should be specified, with a value of the query identifier.
If QUERYID is specified, the STATEMENT element must be defined as a null element and no SQL statement should be specified.
In addition to full format DB2WSE requests, which can specify any SQL statement type (SELECT, INSERT, UPDATE or DELETE), DB2WSE can also be invoked using a simple URL in the REST format. REST-format DB2WSE requests can only be SELECT statements.
The generic format of a REST DB2WSE URL is as follows:
http[s]://{server-address}[:{port}]/db2wse/[{userid}:{password}/]{library}/{file}[/{key}[:{key}...]where each {key} is a value for a key column in the order they are defined for the table being accessed.
The number of key values specified (if any) must be less than or equal to the number of key columns defined for the table being accessed. DB2WSE parses any key values specified and maps them to the correct key column in the table.
Values specified for character key columns will automatically be converted to upper-case - DB2WSE does not currently support mixed-case or lower-case key column values.
When a REST-style URL is used to access DB2WSE, a BASIC response is returned.
The following REST-format URL:
http://red.rpg-xml.com/db2wse/demo:demo/petstore/item/est-1can be specified instead of the following full DB2WSE request:
<DB2WSEREQ>This example assumes that ITEMID is the first key column specified for the PETSTORE/ITEM table.
The following REST-format URL:
http://red.rpg-xml.com/db2wse/demo:demo/petstore/lineitem/1:2can be specified instead of the following full DB2WSE request:
<DB2WSEREQ>This example assumes that ORDERID and LINENUM are the first and second key columns specified for the PETSTORE/LINEITEM table.
When a DB2WSE request is made, DB2WSE will return data to the requestor as XML data. The returned data will generically consist of an outer DB2WSERSP section containing a HEADER section and a data section, e.g.:
<DB2WSERSP>The HEADER section itself contains a copy of the original DB2WSE request, but without the USER or PASSWORD XML elements, e.g.:
Throughout this chapter, the DB2WSE request returned in the HEADER will simply be displayed as {db2wse-request}, e.g.:
<DB2WSERSP>If the DB2WSE request SQL statement is a SELECT statement, the HEADER section will also include the following elements (after {db2wse-request}), which contain further information about the response data:
The DB2WSE response to a SELECT statement depends on the value of the RSPTYPE attribute to the STATEMENT element in the DB2WSE request. The response will consist of either information about the columns (column attributes), the actual row and column data (column data) or both column attributes and column data.
A BASIC response (where RSPTYPE='basic' was specified) will return a HEADER section followed by row and column data as follows:
<DB2WSERSP>The ROWNBR value refers to the row number within the set of rows returned in the DB2WSE response. This may therefore be different to the relative record number (RRN) of the row in the underlying physical table.
For null-capable columns where the column has the null indicator set, the format of the column will be:
<{column-name}/>See the SQL/XML Examples chapter for an example of such a response.
A FULL response (where RSPTYPE='full' was specified) will return separate HEADER and COLDEFS sections containing header information and column definitions, followed by row and column data as follows:
<DB2WSERSP>For null-capable columns where the column has the null indicator set, the format of the column will be:
<C{column-sequence}/>The COLDEF attributes string is made up of the following:
COLSEQ='{column-sequence}' COLNAME='{column-name}' COLTEXT='{column-description}' COLTYPE='{column-type}' COLLENGTH='{column-length}' [COLDECPOS='{column-decimal-positions}']The COLDECPOS attribute is only included with numeric columns.
The COLSEQ attribute is the order of the column in the table: the first column has column-sequence 1, the next column has column-sequence 2 and so on.
The COLTYPE attribute will have one of the following values:
The value may also have 'with nulls' appended (e.g. 'Fixed character with nulls') if the column is null-capable.
A DATAONLY response (where RSPTYPE='dataonly' was specified) will return row and column data in the same format as would be returned for a FULL response, but without the COLDEFS section.
See the FULL response section above for details
A COLDEFS response (where RSPTYPE='flddefs' was specified) will return the Header and COLDEFS sections that would be returned with a FULL response, but without the ROW sections.
See the FULL response section above for details
For non-SELECT statements, the DB2WSE response will have the following format:
<DB2WSERSP>The RSPCODE value indicates the number of succsessful database updates (i.e. it will have the same value as number-of-rows).
If an error occurs in the DB2WSE program, the DB2WSE response will have the following format:
<DB2WSERSP>The PGM value will always be 'DB2WSE'.
The SEVERITY value will be between 0 and 100.
The following is a list of the possible CODE values. A value in parentheses after the explanatory text for each error code specifies the message identifier of the actual text that will be displayed for the TEXT value. The message identifier begins with 'DWE' or 'RX', then the message is held in the KTIMSGF message file. If the message identifier has a value of 'CPF*', then one of several messages specified in the QCPFMSG message file may be used.
or, in REST format
http://red.rpg-xml.com/db2wse/demo:demo/petstore/orders