Warehouse Release 2.07.3360
12/12/2002
There is now a new release of Warehouse available (2.07.3360).
The enhancements in this release are:
Password encryption: Warehouse supports password encryption in the OPEN and CREATE statements. Both login passwords and database passwords may be specified in encrypted format using the EPASS1=parameter to indicate an encrypted password. This enhancement allows Warehouse scripts stored on disk to contain an encrypted password rather than a plain text password. Password encryption uses a proprietary algorithm based on the Data Encryption Standard (DES). Encrypted passwords are automatically generated and used by DataBridger Studio. To generate encrypted passwords with Warehouse, run Warehouse with the "-c" parameter to enter connection testing mode (see below).
The syntax of the OPEN statement with encrypted passwords is now:
OPEN <dbtag> REMOTE USER=<user> EPASS1=<encrypted password>
<dbtype> <dbparms>
<dbtag> is the database tag used in the script.
<user> is the user name on the REMOTE system. MPE/iX logins
may now omit the user, account, and group passwords and
specify them with the EPASS1 parameter.
<encrypted password> This is a 64 character encrypted password. This
value may be generated with DataBridger Studio or by
running Warehouse using -c.
<dbtype> Is the remote database type.
<dbparms> These are the database parameters used to open the database on the remote system. The <dbparms> depend on the type of database opened. EPASS1 is also supported for database passwords using one of the following depending on the <dbtype>:
ORACLE <user> EPASS1=<encrypted db password>
<oracle parms>
ODBC <data source>
USER=<dbuser> EPASS1=<encrypted db password>
IMAGE <root file name> EPASS1=<encrypted db password>
MODE=<db mode>
Examples of EPASS1:
Example 1 - Connecting to an Oracle database on a Unix system:
OPEN dbtag REMOTE sys101 USER=whuser &
EPASS1=2d8e635c6f3b553e7ad844073c417bef84715403220d34b502199547946d04af &
oracle scott SID=ora8 HOME=/u01/oradata/ora sid=ora &
EPASS1=391dc53d8e04e50dadc1a5e829139bfa788b0c3906a3b9b7fc0f11684fd127e2
Example 2 - Connecting to an Image database on an MPE/iX system:
OPEN dbtag REMOTE sys201 USER=MGR.WHUSER &
EPASS1=e93fa8446651cc6f3b512efbf2e9553a56ec64e904d1843424272fb73d640e5c &
image sales.db MODE=5 &
EPASS1=46c59f09b04e50da71dd5609c94d33f992ed7bc61ab7f2eb28bfacab3df78e99
Improvements to wh -c: when Warehouse is run with the "-c" parameter, onnection test mode is entered. Connection test mode has been enhanced to test a database connection in addition to a system connection. If a connection is successful and the remote Warehouse server supports encrypted passwords, then a sample OPEN statement is displayed using encrypted passwords. This OPEN statement may be cut and pasted into a Warehouse to use encrypted passwords in the script.
New == operator: A new operator == has been implemented to improve comparisons between items that may contain nulls. The Warehouse equal (=) operator returns false if either of the two operands being compared is null. This is in accordance with SQL standards. However, users often wish to compare an "old" and "new" value, or a "source" and "target" value and wish true to be returned if both operands are null. The == operator is identical to the = operator, except that == returns true if both operands are null, but = returns false.
Some examples using the follow script:
DEFINE NOTNULL1, NOTNULL2, NOTNULL3, ISNULL1, ISNULL2 : &
ORACLE VARCHAR2(20) ALLOW NULLS
SETVAR NOTNULL1 = "SOME DATA"
SETVAR NOTNULL2 = "SOME DATA"
SETVAR NOTNULL3 = "SOME OTHER DATA"
SETVAR ISNULL1 = $NULL
SETVAR ISNULL2 = $NULL
Expression Result
--------------------------------- ------
1. NOTNULL1 = NOTNULL2 TRUE
2. NOTNULL1 = NOTNULL3 FALSE
3. NOTNULL1 = ISNULL2 FALSE*
4. ISNULL1 = ISNULL2 FALSE*
5. NOTNULL1 == NOTNULL2 TRUE
6. NOTNULL1 == NOTNULL3 FALSE
7. NOTNULL1 == ISNULL2 FALSE
8. ISNULL1 == ISNULL2 TRUE
* Indicates an SQL three valued logic "unknown" condition.
New ESCAPE function: A Warehouse ESCAPE function has been added
to create an ERROR condition. If the ESCAPE function is called,
the script stops running immediately unless a TRY function or
TRY statement is in effect, in which case error recovery takes
place. The new ESCAPE function performs exactly like the ESCAPE
statement. The syntax of escape is:
string = ESCAPE(<error message>)
<error message> is the message displayed when Warehouse exits, or the $ERR.ESCMSG if a TRY is in effect. ESCAPE returns a string to satisfy internal requirements that a function must return some type of value. The value "returned" by escape cannot be accessed.
Examples of ESCAPE:
READ SRC = SRCDB.SOURCE
TRY
SETVAR TAR.ORDNO = &
IF(ISNUMERIC(ORDNO), NUMERIC(ORDNO), ESCAPE("BAD ORDNO"))
SETVAR TAR.COMPNO =
IF(ISNUMERIC(COMPNO), NUMERIC(COMPNO), ESCAPE("BAD COMPNO"))
SETVAR TAR.PRODNO =
IF(ISNUMERIC(PRODNO), NUMERIC(PRODNO), ESCAPE("BAD PRODNO"))
SETVAR TAR.DESC = DESC
COPY TAR TO TARDB.TARGET; WAIT
RECOVER
IF $ERR.WHERRNO = 8202
PRINT "Escape from order", ordno, $ERR.ESCMSG
ELSE
PRINT "Error in order", ordno, $ERR.WHERRMSG, $ERR.DBERRMSG
ENDIF
ENDTRY
ENDREAD
CLOSE statement: The previously unsupported CLOSE statement is now supported. The syntax of CLOSE is:
CLOSE <dbtag>
<dbtag> is tag of the database or file to be closed.
The CLOSE statement immediately closes the database or file and may not be accessed for the remainder of the script. The purpose of CLOSE is to release system resources while the script is running. This typically happens when a database is opened simply to determine the format of tables and the tables are NOT accessed while the scripts running.
Improved timestamping in WHLOG: The timestamp format in the WHLOG file is now DD-MON-YY HH:MM:SS and error messages now contain a timestamp. In prior releases, the timestamp format was DD-MON HH:MM:SS and error message were not timestamped.
Release 2.07.3360 is available now on all supported platforms.