Interesting Warehouse Features
roll-up as of release 3.00.3440
What is the DESCRIBE statement and how might I use it? (2.07.2270)
The DESCRIBE statement displays the layout of a table in various formats including DB2, ODBC, and Oracle create table statements. The purpose of DESCRIBE is to translate a table structure into another data definition language that is usually an SQL variant, in order to create a similar table in the other database. The statement usage is:
DESCRIBE dbtag[.tablename]
[using {db2|odbc|oracle}{creates |whcreates}]
[with {nulls|varchars}]
[to filename]
[resok]
dbtag is the tag of the database being described
tablename is the name of the table to be described. If no tablename is specified all tables in the database will be described
using indicates the type of output. If using is not specified, Warehouse format statements are produced.
db2 indicates that db2 translation is used.
odbc indicates that ODBC (SQLServer) translation is used.
oracle indicates that Oracle translation is used.
creates indicates that CREATE TABLE statements are to be produced.
whcreates indicates that CREATE TABLE statements are to be produced inside of a Warehouse DIRECT statement so that the table may be created by Warehouse.
with indicates options.
nulls indicates that fields that are NOT NULL in the source table are to allow nulls.
varchars indicates that IMAGE X and U fields are to be converted to VARCHAR fields instead of CHAR fields
to filename, where filename is the name of the physical file to which DESCRIBE writes the output of the statement.
resok indicates that SQL reserved words are to be allowed as field names. Without resok Warehouse appends an underscore (_) to fields that are ANSI SQL reserved words.
Example:
1> open x image macord.macsdata pass=homer mode=5
2> describe x to somefile
The resulting file contains all the format statements you will need for the macord database for the BridgeWare scripts. You should generate a file for each database referenced in your mapping document.
What is the CHRONOS data type? (2.07.2320)
A new DATETIME data type CHRONOS has been added to Warehouse. This supports the Chronos data type from SRN Systems and the Lund Performance Solutions Developer’s Toolbox. The functionality and capabilities of the CHRONOS data type are identical to the existing DATETIME data type. Only the storage is different. CHRONOS dates are 6 bytes or 48 bits as follows:
Bits 00..11 Year (0-4095)
Bits 12..20 Day within year (1-366)
Bits 21..25 Hour of day (0-23)
Bits 26..31 Minute of hour (0-59)
Bits 32..47 Millisecond of minute (0-59999)
Typically a CHRONOS field would be stored as an X6 type within a TurboIMAGE database. To access a CHRONOS date stored within an X6 field, the FIELD function should be used to interpret the bytes correctly. For example, if you have a TurboIMAGE dataset named ord with a field named ord-datedefined as an X6 that contains a CHRONOS date, you could print the date using the following Warehouse script:
read x = imdb.ord
print field(ord-date, 1, “chronos”) pic “mm-dd-yyyy)
endread
If you needed to update ord-date to the current date and time, you could use the following script:
define date: chronos
setvar date = $now
read ord = imdb.ord
update ord set ord-date = field(date, 1, “image x6”)
endread
The default settings for the CHRONOS fields are as follows:
1> define c: chronos
2> print c
01-JAN-1900 000000
The CHRONOS definition of dates before 1753 differs from the Julian calendar definition for these same dates. An SRN Support Center Note on this subject reads in part: “The timestamp was designed for modern business purposes, where from a pragmatic point of view only those dates from the 19th, 20th, and 21st centuries are likely to be important”.
Tip: In order to trap invalid date values in a CHRONOS field input from a TurboIMAGE database, wrap the code that handles the CHRONOS field in a TRY/RECOVER block.
What are the Warehouse Bit Operators? (2.07.2440)
A series of operators for performing bit manipulation on integers has been added. These operators can be used to extract and deposit bits within integers. The new bit operators are:
BNOT Bit NOT
Syntax: BNOT
Performs a bitwise NOT operation on each bit in .
BSL Bit Shift Left
Syntax: BSL
Performs a bit shift left of bits on .
BSR Bit Shift Right
Syntax: BSR
Performs a bit shift right of bits on .
BAND Bit AND
Syntax: BAND
Performs a bitwise AND on and .
BOR Bit OR
Syntax: BOR
Performs a bitwise OR on and .
BXOR Bit eXclusive OR
Syntax: BXOR
Performs a bitwise XOR on and .
Negative numbers are treated in 2’s complement form. Examples:
Expression Result
BNOT 0 -1
BNOT 1 -2
BNOT 16 -17
BNOT -1 0
BNOT -17 16
1 BSL 2 4
3 BSL 2 12
5 BSL 3 40
-1 BSL 2 -4
10 BSR 2 2
-10 BSR 2 -3
12 BAND 6 4
11 BAND -2 10
12 BOR 6 14
11 BOR -2 -1
12 BXOR 6 10
11 BXOR -2 -11
(5 BSL 8) BOR 3 1283
(1283 BSR 8) BAND 7 5
The Warehouse operator order of precedence has been enhanced to include the new bit operators. The new operator order of precedence is:
1. NOT Logical not, BNOT Bit not
2. BSL, BSR Bit Shift operations
3. *, /, MOD Multiplication, division, and modulus, BAND Bit and
4. +, – Addition and subtraction, BOR, BXOR Bit or and exclusive or || String concatenation
< br>5.=,<,>,<=,>=,<> Relational operators
6. AND Logical AND
7. OR Logical OR
8. PIC Print picture
What is the CMAP function? (2.07.2780)
The CMAP function has been added to translate between character sets. The syntax of the CMAP function is:
CMAP ( source-string, source-charset, target-charset )
source-string is the string of characters to be translated. It can be any string expression.
source-charset is the name of the source character set as defined in the character map file. (seebelow)
source-charset must be a constant string enclosed in quotation marks.
target-charset is the name of the target character set as defined in the character map file. (see below) The result of CMAP is a string of characters in the target character set.
target-charset must be a constant string enclosed in quotation marks.
CMAP translates source-string from the source-charset to the target-charset by determining the character id of each character in the source-string and then translating each character with
the matching id from the target-set. If there is no id in the source-set or there is no matching id in the target-set, no translation is done and the source character is copied unchanged to the output string.
To support the CMAP function, Warehouse relies on underlying “charmap” files that define each character set. The charmap files are in an an industry standard format and are commonly available on Unix platforms and on the internet (For example, charmaps are available at: http://std.dkuug.dk/i18n/charmaps.646/). (On a Unix system, see “man charmap” for details.)
The charmap files used by Warehouse must be listed in the CHARMAPS file. The CHARMAPS file is created by the user in a text editor and lists the names of the charmap files Warehouse is to access, one file name per line. The CHARMAPS file must reside in the same directory as the Warehouse program.
For example if your Warehouse program is located in the directory /usr/local/taurus/whii/, then your CHARMAPS file must be called:
/usr/local/taurus/whii/CHARMAPS
Your CHARMAPS file might contain the following two lines pointing to your two charmap files:
/usr/lib/localedef/src/iso_8859_1/charmap.src
/usr/local/charmaps/DIN_66003
The following is an example of how to use the CMAP function to translate between standard English and German character sets. We will assume that listed in the CHARMAPS file are references to the locations of both charmap files. On line 7, the string german receives the character translation of the string english. The show charmaps command tells us what charmaps have been loaded.
1> show charmaps
Warehouse character maps:
1. ISO8859-1 1 byte 277 encodings.
2. ANSI_X3.4-1968 1 byte 211 encodings.
2>
3> define english: string
4> define german: string
5>
6> setvar english = “Local config error: relaying denied”
7> setvar german = cmap (english, “ISO8859-1″,”ANSI_X3.4-1968”)
The next example uses pre-processing to defer until run-time the choice of character sets. Warning to those NLS mavens among our readers: the character set names used below are instructional and not precise.
1> define s: string
2> define english: string value “Virtual domains in the syslog”
3> # if defined ( BENGALI )
4* setvar s = cmap ( english , “ISO8859-1″,”Bengali-map”)
5* # else if defined ( HINDI )
6* setvar s = cmap ( english , “ISO8859-1″,”Hindi-map”)
7* # else
8> # print ‘——> no optional maps available, defaulting to english’
——> no optional maps available, defaulting to english
9> setvar s = english
10> # endif
For additional information on CMAP, please reference the 2.07.2780 Release Notes.
What is the SCRUB function? (2.07.3060)
The SCRUB function scans a source string, replacing each character that is less than space (0x20) or greater than a tilde (0x7E) with the specified replacement string. The function returns the result as a string. The syntax is:
result = SCRUB (source-string, replacement-string)
The listing below provides both an example of simple substitution and one of testing a string for out-of-range values.
1> define s: string value “ab\ncd\nef”
2> print s
3> print scrub ( s, “*” )
4> if s <> scrub ( s, “*” )
5> print “string contains out-of-range values”
6> endif
ab
cd
ef
ab*cd*ef
string contains out-of-range values
To remove all binary characters from a string use “” for the replacement string:
1> define s: string value “ab\ncd\nef”
2> print scrub(s,””)
abcdef
What are the DIVF and DIVI functions? (2.07.3060)
The DIVF and DIVI operators have been added to the Warehouse scripting language to perform division.
The DIVF operator performs a floating point divide and returns a floating point result.
The DIVI operator performs an integer divide and returns an integer result.
Users are encouraged to use DIVI and DIVF instead of / to perform division in Warehouse scripts. The type of divide performed by / depends on the data type of the operands and this is not always clear to the user. The / operator has not been changed so the behavior of existing scripts will not be affected.
1> define a, b: odbc double precision
2> define i, j: odbc integer
3>
4> setvar a = 3
5> setvar b = 4
6> setvar i = 5
7> setvar j = 7
8>
9> print “/ “, a / b, a / i, i / a, i / j
10> print “divf”, a divf b, a divf i, i divf a, i divf j
11> print “divi”, a divi b, a divi i, i divi a, i divi j
/ .750000 .600000 1.666667 0
divf .750000 .600000 1.666667 .714286
divi 0 0 1 0
Does Warehouse support Windows user names that contain special characters? (2.07.3060)
Warehouse supports Windows passwords that contain special characters.
When OPENing the data source in a script with a user name or password that contains special characters, you need to delimit these with braces. For example, if the user name is sue reed that contains an embedded space, and the password is sue’s pass that contains both a space and a single quotation mark:
open db remote win012 user={sue reed} pass={sue’s pass} odbc sourceqa
To note, when testing the data source by running the client in a connection-checking mode (with the -c option), the braces are not required. For example:
Enter name or IP address of server -> win012
Enter user name on server (Optional) -> sue reed
Enter password -> sue’s pass
Opening login connection
from client system win008
and client user Administrator
to server system win012
and server user sue reed
Finding win012… Trying 102.111.40.133
Connected to: Warehouse 2.07.3060-W on win012
Connection to Warehouse server win012 SUCCESSFUL.
How do I assign a null value from within the IF(,,) function? (2.07.3060)
The IF(,,) function now supports the assignment of $NULL from the third (false) parameter position.
For example, to set a receiving variable to null if the source variable does not contain a valid numeric:
SETVAR NEW = IF ( ISNUMERIC(OLD), NUMERIC(OLD), $NULL )
Note that $NULL is prohibited as the second (true) value in the IF(,,) function.
Can I initialize a NULLable variable in a DEFINE to null? (2.07.3060)
The DEFINE statement now supports the syntax VALUE $NULL to give an initial value of null to a variable.
In previous versions of Warehouse, null was (and continues to be) the default value for variables defined as allowing nulls, but $NULL could not be explicitly specified. Now it can. For example:
DEFINE AMOUNT: ODBC DECIMAL(10,2) ALLOW NULLS VALUE $NULL
Does Warehouse support the SQL Server UNIQUEIDENTIFIER data type? (2.07.3060)
Support for SQL Server unique identifiers has been added by providing the ODBC UNIQUEIDENTIFIER data type. ODBC UNIQUEIDENTIFIER values are 16 bytes in length and are generally
treated by Warehouse the same as binary types.
Can I skip a column when COPYing to an ODBC table? (2.07.3060)
Columns may be now be skipped when copying a record to an ODBC table. Normally when copying any record to a target table Warehouse writes every column to the target table. Fields not explicitly set by the script are internally initialized by Warehouse and the value set by Warehouse is written to
the table. This method of writing causes the SQL target to bypass any default column values, since values are always set by Warehouse. The COPY statement has been enhanced to allow columns be skipped, thus causing the SQL default value to be used. The partial syntax of the COPY statement when copying to an ODBC table is:
COPY record TO db-tag.target-table (SKIP column)
Other COPY parameters may still be used and more than one column may be skipped by separating the SKIP parameters by a comma. For example,
COPY record to db-tag (SKIP column1, SKIP column2, SKIP column3)
SKIP supports the {identifier} syntax for case sensitive databases, e.g. ( SKIP {column_id} )
SKIP is only supported for ODBC targets.
Does Warehouse support C++ type in-line comments “//”? (2.07.3060)
Built into the current version of WH is support for C++ type in-line comments.
The rule is simple: any text after the comment symbol is considered a comment until a logical end-of-line is encountered. In other words:
setvar a = b + 1 // a simple assignment that is valid
setvar a = &
b + 1 // this is still valid
setvar a = & // this will generate a syntax error
b + 1
Where this form of comment is useful is in labeling END, for instance ENDREADs within nested READs, or when describing FORMAT elements. For example:
read x = db.emp
read y = db.dept for deptno = x.deptno // find match
endread // of dept table
endread // of emp table
format afmt: record
name: x36 // full name including middle initial
rank: x16 // as of 1/1/2002
serialnumber: x16 // domain is 1000 to 3000
end
Does Warehouse run on Linux? (2.07.3070)
As of the 2.07.3070 release, Warehouse runs on Red Hat version 7.3 and supports the Oracle 9i release.
What is the new Warehouse identifier length? (2.07.3070)
Warehouse identifiers may now be up to 80 characters long, an increase from the existing 31 character length limit.
Does Warehouse support the ODBC BIGINT data type? (2.07.3100)
As of the 2.07.3100 release, Warehouse supports the ODBC BIGINT data type for 64 bit integers. All native SQL Server data types are now supported by Warehouse.
What is $NOW0? (2.07.3140)
$NOW0 , like $NOW, is a DATETIME system variable. Whereas $NOW is recomputed each time it is invoked during the execution of a script, $NOW0 is established once during script compilation and will not change.
Does Warehouse support the Oracle 9i TIMESTAMP data type? (2.07.3140)
As of the 2.07.3140 release, Warehouse supports the Oracle 9i TIMESTAMP data type. This data type is defined internally to Warehouse as an ORACLE TIMESTAMP and represents a date and time to a nanosecond of precision. An ORACLE TIMESTAMP is an 11-byte value where bytes 1-7 are the same as an ORACLE DATE, and bytes 8-11 represent the nanosecond within the second expressed using a 32 integer in big-endian format
What are transactional flat files? (2.07.3140)
Flat files now support COMMIT and ROLLBACK. A TEXT, FIXED, or CSV type file opened with mode “!wc+” will now participate commit and rollback transaction processing.
This capability is implemented internally in Warehouse by keeping track of all file changes in internal buffers. If the file is subsequently rolled back, all changes to the file are discarded. If a commit is issued, then all changes are written to disk.
These transactional flat files behave like transactional RDBMS files, observing the implied commit rate of a script, or the defined global commit rate (SET COMMITRATE), or the explicit COMMIT and ROLLBACK statements.
What is the TRY function? (2.07.3150)
The TRY function is used to catch an error during expression evaluation. The syntax of TRY is:
TRY(expression, error-result)
If expression evaluates without error, then the result of the TRY function is the expression. If an error or warning is encountered during the evaluation of expression, then error-result is
returned. The data type family of error-result must be the same data type family of expression.
Examples:
SETVAR N = TRY(CONVERT(X, “IMAGE I1”), -1)
Tries to convert X to an IMAGE I1 and returns -1 if there is a conversion error.
SETVAR N = TRY(NUMERIC(S), &
TRY(NUMERIC(STR(S, 1, LEN(S)-1)), $NULL))
Tries to convert S to a number. If there is a conversion error, then the conversion is attempted once again without using the last character of S. If that also fails, $NULL is returned.
NOTE: Care must be used when using the TRY function. The following are NOT equivalent:
* option 1
TRY
SETVAR A = B
RECOVER
SETVAR A = 0
ENDTRY
is not the same as:
* option 2
SETVAR A = TRY(B, 0)
The reason options 1 and 2 are not equivalent is that before an assignment is performed, the value to be assigned is converted to the data type of the recipient variable. This conversion may cause an error, which would be caught by option 1 but not by option 2. In option 1 the equal (=) operator is covered by the TRY, but in option 2 the equal operator is outside of the TRY.
A way to solve this problem is to convert the result to the target type inside of the TRY. For example, if the data type of A was an IMAGE I1, the statement would look like:
* option 3
SETVAR A = TRY(CONVERT(B, “IMAGE I1”), 0)
This solution works fine for binary integer types such as an IMAGE I1, but can still fail to catch errors for certain data types, such as IMAGE Z types. For example, if both A and B are IMAGE Z6 and you wish to catch a bad value of B, the following will *not* work:
* option 4 (Bad)
SETVAR A = TRY(CONVERT(B, “IMAGE Z6”), 0)
The reason is that since B is already type IMAGE Z6, no conversion is actually done and bad data inB is not caught. The way to catch a bad B is to perform an operation on B, such as add zero. Example:
* option 5 (Good)
SETVAR A = TRY(B + 0, 0)
To make certain there is no conversion error for the assignment and that the data is good, you can combine the convert and add zero techniques. Example:
* option 6 (Best)
SETVAR A = TRY(CONVERT(B, “IMAGE Z6”) + 0, 0)
Can wh -c test a database connection in addition to a system connection? (2.07.3360)
When the Warehouse client is run with the “-c” parameter, connection test mode is entered. Connection test mode has now 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 script to use encrypted passwords in the script. For example, see the following session output:
sol08 /users/qa %wh -c
Warehouse 2.07.3360-S (c) Taurus Software, Inc. 2002
Warehouse Server Connection Verification.
Enter name or IP address of server -> alpha02
Enter user name on server (Optional) -> qa
Enter password -> homer
Enter remote database type (optional) -> oracle
Enter Oracle user name -> scott
Enter Oracle user password -> tiger
Enter Oracle SID -> ora817
Enter Oracle HOME -> /c/u01/app/oracle/product/8.1.7
Opening login connection
from client system sol08
and client user qa
to server system alpha02
and server user qa
Finding alpha02… Trying 160.47.17.206
Connected to: Warehouse 2.07.3360-A on alpha02
Warehouse OPEN statement with encrypted passwords for alpha02:
OPEN dbtag REMOTE alpha02 USER=qa &
EPASS1=1a33615b54e94fe838724663c10637a0a956e07dd2d09453416e4d9953b4fa79 &
oracle scott SID=ora817 HOME=/c/u01/app/oracle/product/8.1.7 &
EPASS1=3c7ee933c217d63cb8621a0021785fcab5ee54b24b95efc5e68e5341595bf1aa
Connection to Warehouse server alpha02 SUCCESSFUL.
Does Warehouse support password encryption? (2.07.3360)
Warehouse now 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 they can also be used by DataBridger Studio.
To generate encrypted passwords with Warehouse, run Warehouse with the “-c” parameter to enter connection testing mode (see Can I test a database connection in addition to a system connection?above).
The syntax of the OPEN statement with encrypted passwords is now:
OPEN REMOTE USER= EPASS1=
is the database tag used in the script.
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.
is a 64 character encrypted password. This value may be generated with DataBridger Studio or by running Warehouse using -c.
is the remote database type.
are the database parameters used to open the database on the remote system. The depend on the type of database opened. EPASS1 is also supported for database passwords using one of the following depending on the :
ORACLE EPASS1=
ODBC USER= EPASS1=
IMAGE EPASS1= 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
What is the new ESCAPE function? (2.07.3360)
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()
where 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.
Here are some xamples of the ESCAPE function:
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
What is the new CLOSE statement? (2.07.3360)
The CLOSE statement is a compile-time operation that immediately closes an open database or file. The syntax of the CLOSE statement is:
CLOSE
where is tag of the database or file to be closed.
The database or file may not be accessed for the remainder of the script. The purpose of CLOSE is to provide an early release of system resources. This typically is desired when a database is opened simply to determine the format of tables and these tables are NOT accessed while the script is running.
What is the == operator? (2.07.3360)
A new operator == has been implemented to improve comparisons between items that may contain nulls. The Warehouse equal (=) operator returns FALSE if either or both of the two operands being compared is null. This is in accordance with SQL standards. However, users often wish to compare an old and a new or a source and a target value, and they wish for TRUE to be returned if both operands are null.
The == operator is identical to the = operator, except that in addition == will return TRUE if both operands are null. Next are some examples of this:
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
Creating comparisons expressions that use these variables will generate the following results:
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.
What is the PASS= parameter when OPENing an Oracle database? (2.07.3460)
ORACLE databases may now be opened using one of the following two methods:
OPEN tag ORACLE user/password SID=sid HOME =home
OPEN tag ORACLE user PASS =password SID=sid HOME =home
What is the virtual table named $DATASETS? (2.07.3520)
A virtual IMAGE table called $DATASETS has been implemented that contains information about each of the datasets in the database.
$DATASETS is accessed with the READ statement as though it were a real dataset within the database. FOR and ORDER BY may also be used. $DATASETS is read only and has the following fields:
SETNAME: X16 Name of the IMAGE data set
SETTYPE: X2 Dataset type: A, D, or M
ENTRYLEN: I1 Number of double-bytes in each record
BLOCKFACT: I1 Blocking factor of the records
NUMENTRIES: I2 Number of records in the dataset
CAPACITY: I2 Maximum number records dataset can hold
Example:
OPEN SALES IMAGE SALES.DB PASS=HOMER MODE=5
READ X = SALES.$DATASETS FOR SETTYPE = “M”
PRINT SETNAME, ENTRYLEN * 2
ENDREAD
What are the IMAGE database limits that Warehouse supports? (2.07.3530)
Limits have been increased for IMAGE databases. The new limits are:
Maximum number of datasets in a database: new limit 240 old limit 199
Maximum number of data items in a database: new limit 1200 old limit 1023
Maximum number of paths in a master dataset: new limit 64 old limit 16
What are the size limits for IMAGE P and Z fields? (2.08.0010)
The maximum size of IMAGE P and IMAGE Z fields has been increased to 254.
How have Warehouse validation codes changed? (2.08.0010)
Warehouse validation codes have been completely reworked. All validation codes are now 20 hexadecimal (0-9 and A-F) digits in four groups of five. For example: C6EA-BE8A-E238-AE1E-7DAC
The 20 hexadecimal digits are used to validate Warehouse for trial, for production, and for changing Warehouse capabilities.
All releases of Warehouse with a version number of 2.08 or greater use the new validation codes.
What is the APPEND hint on inserts to an Oracle target? (2.08.0090)
Warehouse now supports an append hint for COPY statements to an Oracle target.
The syntax is:
COPY rec TO db-tag.target-table (HINT APPEND)
Using the append hint adds the APPEND hint to the Oracle INSERT statement used to copy the data and may increase the performance when writing to an Oracle table. See the Oracle SQL Reference Manual for more information.
What is the NNOT operator? (2.08.0090)
The NNOT operator performs a logical NOT on a boolean expression and is the equivalent to the NOT operator except that NNOT returns true for a null operand. Example:
Given these definitions
DEFINE A: ODBC CHAR(10) ALLOW NULLS VALUE “A”
DEFINE B: ODBC CHAR(10) ALLOW NULLS VALUE “B”
DEFINE N: ODBC CHAR(10) ALLOW NULLS VALUE $NULL
The results are:
Expression Value
NNOT (A < B) False
NNOT (A < N) True
NOT (A < B) False
NOT (A < N) Unknown
What is the TOKEN function? (2.08.0090)
A TOKEN function has been added that parses a string and returns
the Nth token within the string. The syntax is:
token = TOKEN (source-string, token-number, “delimiters-flags”)
source-string
is the string to be parsed.
token-number
is the token number to be returned with 1 being the first token in the string. If token number is less than 1 or greater than the number of tokens in source-string a string of zero length is returned.
delimiters-flags
is a constant string enclosed in quotation marks that indicate the parsing delimiters and flags. The delimiters may be any special characters such as comma, colon, semicolon and space. In addition to special characters the S and Q flag is available.
S flag – Indicates that leading a trailing spaces are stripped from the token.
Q flag – Indicates that tokens may be enclosed in quotation marks.
Examples:
if A = ‘one;two,”three,four”;five, six , seven,’
and B = ‘ alpha beta|gamma delta’
Expression Result
TOKEN(A, 1, “,”) one;two
TOKEN(A, 1, “;”) one
TOKEN(A, 2, “;”) two,”three,four”
TOKEN(A, 2, “,q”) three,four
TOKEN(A, 2, “,;”) two
TOKEN(A, 4, “,;”) four”
TOKEN(A, 4, “,;q”) five
TOKEN(A, 5, “,;q”) six
TOKEN(A, 5, “,;qs”) six
TOKEN(B, 1, ” “)
TOKEN(B, 1, ” s”) alpha
TOKEN(B, 3, ” s”) delta
TOKEN(B, 3, ” |s”) gamma
What is the enhanced Character Set support? (3.00.1400)
String data types may now be associated with a character set. To assign a character set, append CHARSET “charset-name” when specifying a data type. The character set name must be enclosed in quotation marks.
Examples:
DEFINE MYVAR: ORACLE VARCHAR2(20) CHARSET “ISO8859-1”
DEFINE CNAME: ODBC CHAR(10) ALLOW NULLS CHARSET “ANSI_X3.4-1968”
When specifying both a CHARSET and ALLOW NULLS, the keywords may appear in either order.
When operations with strings of differing character sets are performed, an automatic character set conversion is done using the CMAP function.
The operations that can generate an automatic CMAP are:
Comparison operators <, <=, =, >=, >, <>, ==
String assignment SETVAR statement, UPDATE statement
String concatenation ||
Strings may or may not have a character set. When a string operation is performed, no character set conversion is done if either string has no character set or if the the strings have the same character set. Conversion is only done when both strings have a character set and the two character sets differ.
Automatic character set conversion may be overridden using the CONVERT or FIELD functions using a target type with no character set or a different character set.
For example, the following two code snippets have the result in the same value in TGTNAM:
Snippet 1:
DEFINE SRCNAM: IMAGE X8
DEFINE TGTNAM: ODBC CHAR(8)
SETVAR TGTNAM = CMAP(SRCNAM,”HP-ROMAN8″, “ISO8859-1”)
Snippet 2:
DEFINE SRCNAM: IMAGE X8 CHARSET “HP-ROMAN8”
DEFINE TGTNAM: ODBC CHAR(8) CHARSET “ISO8859-1”
SETVAR TGTNAM = SRCNAM // Auto CMAP done here
Databases may be assigned a character with the SET statement. When a database is assigned a character set, all character items from the database will be interpreted as belonging to the specified character set.
The syntax is: SET db-tag CHARSET “charset-name”
OPEN SRCDB IMAGE …
SET SRCDB CHARSET CHARSET “ANSI_X3.4-1968”
SET CHARSET must be issued after the OPEN but *BEFORE* any other statements access the database.
What is the ARRAYIFY function? (3.00.1600)
SQL (Oracle and ODBC) table layouts may now be interpreted to contain arrays. To enable this feature for a database, use the SET ARRAYIFY statement.
The syntax is: SET db-tag ARRAYIFY ON
OPEN SRCDB ORACLE …
SET SRCDB ARRAYIFY ON
When SET ARRAYIFY ON is performed on a database, consecutively named columns are interpreted by Warehouse as belonging to an array. For example, if a table format is:
COMPANY_ID: ODBC CHAR(10)
COMPANY_NAME: OCBC CHAR(60)
QTR_1: OCBC DECIMAL(16,2)
QTR_2: OCBC DECIMAL(16,2)
QTR_3: OCBC DECIMAL(16,2)
QTR_4: OCBC DECIMAL(16,2)
YEAR_TOT: ODBC DECIMAL(16,2)
If ARRAYIFY is ON, the table will be interpreted by Warehouse as:
COMPANY_ID: ODBC CHAR(10)
COMPANY_NAME: OCBC CHAR(60)
QTR_: ARRAY [1..4] OF OCBC DECIMAL(16,2)
YEAR_TOT: ODBC DECIMAL(16,2)
QTR was identified as an array because of adjacent and consecutively numbered columns of the same type. The purpose of SET ARRAYIFY ON is allow WHILE loops to access columns as though they were arrays. Unless a WHILE loop is necessary to access consecutively columns, this feature should not be used.
SET ARRAYIFY must be issued after the OPEN but *BEFORE* any other statements access the database.
NOTE: This feature subject to change in the future.
What is the ISDIGITS function? (3.00.1720)
ISDIGITS scans a string and returns true if all characters in the string are “0”-“9”. If the string contains any characters other than “0”-“9” (including spaces) false is returned. A minimum string length may be specified. When a minimum length is specified, false is returned if the length of the string is less than minimum. A zero length string returns false,
unless a minimum length of zero was specified.
result = ISDIGITS(source-string [, minimum-length] )
source-string is the string to be checked for only digits.
minimum-length is an optional parameter that indicates the minimum length of the string. This parameter is often necessary when checking fixed length strings because spaces are truncated from a fixed length string prior to the ISDIGITS check.
Examples:
Expression Result
ISDIGITS(“3141”) TRUE
ISDIGITS(” 3141″) FALSE
ISDIGITS(“-3141”) FALSE
ISDIGITS(“31.41”) FALSE
ISDIGITS(“3141”, 6) FALSE
ISDIGITS(“7F”) FALSE
ISDIGITS(“”) FALSE
ISDIGITS(“”, 0) TRUE
What is the ISNUMZ function? (3.00.1720)
ISNUMZ determines if a string is a valid zoned decimal string that can be converted using the NUMZ function.
To be a valid zoned decimal string, all positions except the last must contain “0”-“9”. The last position of the string must contain one of:
“0” – “9”: indicating an unsigned number
“{“: positive, with last digit of 0.
“A” – “I”: positive, with last digit1-9(A=1,I=9)
“}”: negative, with last digit 0.
“J” – “R”: negative, with last digit1-9(J=1,R=9)
result = ISNUMZ(source-string)
source-string is the string to be checked be a valid zoned decimal string.
Examples:
Expression Result
ISNUMZ(“3141”) TRUE
ISNUMZ(” 3141″) FALSE
ISNUMZ(“-3141”) FALSE
ISNUMZ(“31.41”) FALSE
ISNUMZ(“7F”) TRUE
ISNUMZ(“7Z”) FALSE
ISNUMZ(“}”) TRUE
ISNUMZ(“”) FALSE
What is the NUMZ function? (3.00.1720)
NUMZ converts a zoned decimal string to a number. See ISNUMZ above for description of a zoned decimal number.
If the string passed to NUMZ is not a valid zoned decimal string, a warning is issued and the result is undefined. (The TRY function or statement can used to catch the warning.)
result = ISNUMZ(source-string)
source-string is the string to be checked be a valid zoned decimal string.
Examples:
Expression Result
ISNUMZ(“3141”) 3141
ISNUMZ(” 3141″) Error
ISNUMZ(“7F”) 76
ISNUMZ(“K”) -2
What is the TOKENCOUNT function? (3.00.3100)
The TOKENCOUNT function has been added that parses a string and returns the number of tokens in the string.
TOKENCOUNT parses exactly like the TOKEN function, but returns the number of tokens rather than a specific token. The syntax is:
n = TOKENCOUNT(source-string, “delimiters-flags”)
source-string is the string to be parsed.
delimiters-flags is a constant string enclosed in quotation marks that indicate the parsing delimiters and flags. The delimiters may be any special characters such as comma, colon, semicolon and space. In addition to special characters the S and Q flag is available.
S flag – Indicates that leading a trailing spaces are stripped from the token.
Q flag – Indicates that tokens may be enclosed in quotation marks.
Examples:
A = ‘one;two,”three,four”;five, six , seven,’
B = ‘ alpha beta|gamma delta’
Expression Result
TOKENCOUNT(A, “,”) 5
TOKENCOUNT(A, “;”) 3
TOKENCOUNT(A, “;”) 3
TOKENCOUNT(A, “,q”) 5
TOKENCOUNT(A, “,;”) 7
TOKENCOUNT(A, “,;”) 7
TOKENCOUNT(A, “,;q”) 6
TOKENCOUNT(A, “,;q”) 6
TOKENCOUNT(A, “,;qs”) 6
TOKENCOUNT(B, ” “) 7
TOKENCOUNT(B, ” s”) 3
TOKENCOUNT(B, ” s”) 3
TOKENCOUNT(B, ” |s”) 4
What is the maximum width of an ODBC DECIMAL? (3.00.3440)
The maximum width of the ODBC DECIMAL data type has been increased to 38 from a prior maximum of 28.