Warehouse FAQTable of ContentsWhat is the current released version of Warehouse ? How do I
download a version of Warehouse ? What is the
significance of the suffix in a Warehouse version identifier
? How do I uncompress a downloaded Unix
version of Warehouse ?
What is validation ? How can I
find my Warehouse version number ? Does
Warehouse have an .ini file, and if so what are its specifics
? What happens
if during disaster recovery I restore Warehouse to a backup system ? Why does
Warehouse change its validation state when I change either my MPE/iX
HPSUSANNAME or HPCPUNAME ? How do I
download a copy of the Warehouse Manual ?
Is a
Warehouse script able to access more than one database ? On what port
number does the Warehouse Server listen ? Is a
Warehouse script required to execute on a particular machine
? What’s the
difference between the START and XEQ commands ? What’s the difference
between a FORMAT and a DEFINE ? What are the
respective effects of the ;NOWAIT (the default) and
;WAIT directives in the context of a remote COPY command within a READ loop ? What are the
reserved words in Warehouse? What are the
scope rules for Warehouse variables ? What is the
scope of the Warehouse comment character ? How can I reference
tables or columns with lower-case or mixed-case names ? How are
transactions handled within Warehouse functions ? What is
COMMITRATE and how should I think about it ? What
COMMITRATE is appropriate when using a Message File ? What happens
to my open transaction when I ESCAPE out of the script
? How do I
compare a date that is in YYYYMMDD character form to today’s date ? How can I
determine the day of the week for today's date
? How do I
calculate the number of days between today and a supplied constant
? Where can I
find the result types of addition and subtraction operations between date operators ? What is the syntax for setting columns of type Oracle DATE
? What happens
if I neglect to set an element in a variable
defined as a Warehouse DATETIME data type ? How can I
store a date/timestamp in milliseconds into a variable ?
What are some
of the ways that preprocessing can be useful to me ? Why does my
script fail when I access an environmental variable while preprocessing
? What happens if
I modify an environmental variable while preprocessing ? How can I
determine what lines of preprocessing code have and haven't been executed ? How can I code a script
to conditionally compile based on platform type ? What is the
difference between the DIRECT and CALL DIRECT statements ? Can I call an
Oracle stored procedure from within my Warehouse scripts ? Can you give
me an example of how to capture a
non-tabular return parameter from a SQL query ? Can you tell
me if it's possible to call an external COBOL or C subroutine from Warehouse ? Can I pass
commands to the underlying system ? How can I
send email from a Warehouse script ? How can I
send a script listing and compile errors to the same file ? I have a
number of Unix servers running Warehouse. Is there a way to check on the
status of the WHSERV processes beyond invoking ps through remsh ? When my
production Solaris server is active and I run ps -aef on it, I notice
that there are multiple copies of the WHSERV processes running. What is this
all about ? How does the
Warehouse server handle a remote login in a Unix environment
? What’s an
Archive type file ? After I write
floating point data to a TEXT format file why does the data look wrong when
inspected in the editor ? After I write
floating point data to a FIXED format file why does the data look wrong when
inspected in the editor ? How do I OPEN
an MPE/iX KSAM file ? How can I read a sorted CSV type file ? How can I
match on nulls ? If I have an
all-space field in a TurboIMAGE record, why does Oracle replaces the space in
that field with a null when the record is inserted into an Oracle table ? What is the
length of a CHAR or a VARCHAR2 variable when its contents are null ? Is a
Warehouse script able to read keyboard input ? What is the
file named WHLOG ? How do I find
WHLOG on an MPE/iX system ? How do I find
WHLOG on a Unix system ? What is the
file named WHVALLOG ? How do I find
WHVALLOG ? How can I pause before exiting my client and losing my DOS
box ? Is there a
way to write $ERR (within RECOVER) to a file ? What
additional details might I want to know about $ERR ? What can I
find out about the database that I have opened with a Warehouse client ? What can I
find out about a table in the database that I have opened with a Warehouse client ? What can I find out about my local Warehouse operating environment ? What can I find out about a remote Warehouse operating environment ? Is there an
easy way to initialize a record buffer ? Is there a
way of determining how long it takes the Warehouse engine to compile the Warehouse
script and open the databases ? How can I
prevent the cleartext password provided in my database OPEN from
printing on the Warehouse script's stdout ? How can I
stop Warehouse from stripping trailing spaces from fixed-length columns ? Can I create
or access a SQL View from a script ? Can I use a
record-level move instead of moving each field one at a time
? I'm running
GlancePlus/iX on MPE/iX : why is Warehouse's CPU
utilization high ? Does
Warehouse have a conditional expression like C's expr1 ?
expr2 : expr3 ? How do I
perform an orderly shutdown of Bridgeware ? How can I
look at the raw bits in a field ? Is there a
SQL LIKE predicate selection operator for a Warehouse READ
? How can I
create invalid TurboImage data on the fly for testing purposes
? Why can't
Warehouse access a table that I know exists ? (WHERR
18009) Why do I get
the following loader error when running Warehouse against Oracle 8i on Solaris ? What library references
are needed to run Warehouse against Oracle 9i on Solaris
? Why do I get
a WHERR when I try to COPY data to a text type output file
? (WHERR 8128) Why won't a
backslash print correctly in my script ? Why doesn't a
script created with a Windows editor execute correctly on the HP3000 ? Why do I get
an error when I try to open up an MPE/iX
Message file with mode=v ? Why can't I
remotely access SQL Server on an NT4.0 machine ?
Why can't I
log in remotely to an NT4.0 machine ? Why do I get
a "table does not exist" error after I've just created it ? Why can't I
demo validate my Warehouse instance on MPE/iX ?
Why do I get
a networking error when I OPEN a remote database ? Why do I see
a WHERR 20001 when connecting remotely ? Why do I get
a LDERR 505 when starting WHSERV on the HP3000 ? Why do I get
a WHERR 18002 when opening an Oracle data base ? Why do I get
a WHERR 21037 when reading a SQL Server database defined with a binary collation ? Why do I get
a WHERR 6006 (Illegal sign in packed decimal number) ?
Why do I get
a Win32 error 183 when I re-start my Windows Warehouse Service
? Why do I get
an error -623 when I install Warehouse on Windows ? Why do I get
an MPE/iX FSERR 151 when reading from a Message File ?
Why do I get
a logon_cmd error when OPENing an MPE/iX database ? Why do I get
an ORA-12545 when OPENing an ORACLE database ? Why do I get
a TurboIMAGE error -1858 at the conclusion of a script run
? What is the
dynamic locking error I get when OPENing a file on MPE/iX ?
Why can't my
MPE/iX system ping itself when other systems can ping it ?
What MPE/iX
JCWs are set on a failed OPEN ? How can I fix a Warehouse client's "Out of new
memory" condition on MPE ? What does "Error dynamically loading getspwnam()"
mean ? What does Invalid client
program. (WHERR 20001) mean ? Why do I
get the message Warehouse validation error number 20 ? What does
Decimal number overflow (WHWARN 5267) mean ? new Can I read Oracle
V$ views from within Warehouse ? Can I make
use of an Oracle SEQUENCE from within Warehouse ? Can I read to
or write from a Unix FIFO (named pipe) in a
Warehouse script ? Can I use the
Oracle bulk loader with Warehouse on Unix to insert to an empty table ? Can I invoke
the Microsoft Bulk Copy Program from a Warehouse script ? How do I copy
MPE/iX Capture Files using Warehouse ? What is the
default TurboIMAGE locking and how do I obtain more concurrency
? Will a
Warehouse client run within the MPE/iX POSIX shell ? Will
Warehouse run on Windows 95 or Windows 98 ? Will
Warehouse run with MPE/iX BIGPIN enabled ? How can I
test for completion of a script running on MPE/iX ? Are there any side-effects to the "Boost SQL Server Priority" option in SQL Server ? Why does my script run slower against a SQL table once I have completed an initial load ? What does the
Warehouse server require on an HP-UX Trusted System ?
Can Warehouse
make use of Third Party Indexing ? When I access a TurboIMAGE dataset specifying a READ... FOR
condition and the access uses a TPI , why do I see
the message 'Using serial read' in the standard list after the READ statement
? Can you help
with the following DBFIND error that I get when accessing a TurboIMAGE database ? (WHERR 10039) Why do I get
an error when I try to READ...FOR using a key that I've defined in my TPI environment ? Why do I get
a WHERR 8007 when I use an ESCAPE statement ? How can I exit prematurely from a script ? Why won't my
script branch to RECOVER on a COPY error ? Major
new Warehouse features roll-up as of
release 3.00.3440 See Release Notes for new Warehouse Features subsequent to release 3.00.3440. What is the
DESCRIBE statement and how might I use it ? What is the
CHRONOS data type ? What are the
Warehouse Bit Operators ? What is the
CMAP function ? What is the
SCRUB function ? What are the
DIVF and DIVI functions ? Does
Warehouse support Windows user names that contain special characters
? How do I
assign a null value from within the IF(,,) function
? Can I
initialize a NULLable variable in a DEFINE to null ? Does
Warehouse support the SQL Server UNIQUEIDENTIFIER data type
? Can I skip a
column when COPYing to an ODBC table ? Does
Warehouse support C++ type in-line comments "//"
? Does
Warehouse run on Linux ? What is the
new Warehouse identifier length ? Does
Warehouse support the ODBC BIGINT data type ? What is $NOW0 ? Does
Warehouse support the Oracle 9i TIMESTAMP data type ? What are
transactional flat files ? What is the
TRY function ? Can wh -c
test a database connection in addition to a system connection
? Does Warehouse
support password encryption ? What is the
new ESCAPE function ? What is the
new CLOSE statement ? What is the
== operator ? What is the
PASS= parameter when OPENing an Oracle database ?
What is the
virtual table named $DATASETS ? What are the
IMAGE database limits that Warehouse supports ?
What are the
size limits for IMAGE P and Z fields ? How have
Warehouse validation codes changed ? What is the
APPEND hint on inserts to an Oracle target ? What is the
enhanced Character Set support ? What is the
TOKENCOUNT function ? What is the
maximum width of an ODBC DECIMAL ? Which ODBC
sources can function as a Studio Repository ? What is the
recommended ODBC data source type for a Studio Repository ? Is a Studio
Repository intended for concurrent usage ? How then do I
obtain exclusive access to a Studio Repository ? Why do ODBC
CHAR data types contain an extra byte when used in FORMATs
? Installation What is
the current released version of Warehouse ? The current released
version of Warehouse is 3.02.4410. How do I
download a version of Warehouse ? Current
versions of Warehouse for all supported platforms are available at the Taurus download web
site. The releases of Warehouse for supported Unix platforms are tar'd and compressed : you will need to run uncompress (or equivalent) and tar in order to extract the files. The release of Warehouse for Windows is in zip format : you will need to un-zip this packages in order to extract the files. See the
Warehouse Reference Manual for installation and validation details, or,
alternatively, the appropriate section in the Databridger
Quick-Start Guide. What is the significance of the
suffix in a Warehouse version identifier ? The trailing suffix designates the platform that the
Warehouse distribution has been built to run on. For example, 3.00.3440-W
runs on Windows. The list of suffixes follows: F - Hewlett-Packard HP-UX PA RISC 2.0 (64-bit) G - Hewlett-Packard HP-UX Itanium (64-bit) H - Hewlett-Packard HP-UX PA RISC 1.1 (32-bit) L - Red Hat Linux x86 M - Hewlett-Packard 3000 MPE/iX operating system. R - IBM RS6000 system running AIX S - Sun Microsystems system running SunOS/Solaris T - Debian Linux ia64 U - Microsoft Windows/XP operating systems 64-bit W - Microsoft Windows/XP operating systems 32-bit How do I uncompress a downloaded Unix version of Warehouse ? If the package has a .Z suffix, use the uncompress program, usually found in /usr/bin. If the package has a .gz suffix, use the gunzip program, usually found in either /usr/local/bin or /user/contrib/bin. What is validation ? Validation is
an operation that allows an instance of Warehouse to run . There are two
types of validation, demonstration and production. A demonstration validation
is temporary, usually valid for a month. A production validation does not
expire unless the system characteristics change. Both validation types permit
an instance of Warehouse to run in full function mode. Validation is
easier to understand if seen in context. When Warehouse is first installed on
a system, it requires a validation code in order to run. This code is usually
a demonstration code and it is included with the installation media. In order to
enter the demonstration code into the system, you run the Warehouse client
you will use in production with the validation parameter, -v. This parameter
will cause Warehouse to enter into a special validation mode and display the
following menu. A Windows example : D:\ >
wh.exe -v *
Warehouse Validate Menu * Enter 0 to
Exit Enter your
selection ->1 You select
choice 1 for demonstration validation, and enter the demonstration code that
came with the media, in this case LKPK-21119. Provided that the code has not
lapsed and has not been mis-entered, the Warehouse instance will be validated
and display the expiration date and the production return code unique to this
instance. Remember when
installing validation codes that you must be the owner of the Warehouse
files, have write permissions to the Warehouse directory, and that the
Warehouse server must be down. The
production return code is not the validation code. When the Warehouse
instance is to be production validated, please call Taurus support. The
support representative will ask you to run your Warehouse client, again with
the –v parameter. The Warehouse client will list banner lines specifying
expiration date of the instance and its unique validation code, and then
present the validation menu once again. The Taurus
support representative will then ask you for the production return code,
178-545-709-7 in this example, and next return you another number, the
production validation code, 304170 in this example. This time you will select
choice 2 for production validation, enter the unique production validation
code and your company name, and you’re validated. This
version of Warehouse expires on 31-AUG-2000 *
Warehouse Validate Menu * Enter 0 to
Exit Enter your
selection ->2 Taurus
Software thanks you for your purchase of Warehouse. Installed
for: AGX Reference Press It is
important to note that production validation or demonstration re-validation
will never delete any existing settings that you may have configured,
including your AUTHFILE, nor any scripts that you
may have written. How can I
find my Warehouse version number ? If Warehouse is
running under a demo code, how can I tell if it is and if so when the demo
license will expire ? If you run
Warehouse from the command line with no parameters, the first banner line
lists the Warehouse version number in the format n.nn.nnnn-x where x is the
platform identifier. For example : Warehouse
2.07.2240-W (c) Taurus Software, Inc. 2000 where 2.07.2240-W is a version number of Warehouse built for
the Windows environment. If the
instance of Warehouse has been validated for production, the second banner
line will state : Installed
for: <company name> where
<company name> is the information you entered at the time of production
validation If instead
the second banner line reads : This
version of Warehouse expires on dd-mmm-yyyy then your instance of Warehouse is running under a demo
license and is due to expire on the listed date. Does
Warehouse have an .ini file, and if so what are its specifics
? Warehouse has
an initialization file that is named wh.ini or WHINI. In order to make use of
it, a file by this name should be created as a text-type file and placed in
the same directory as the Warehouse executables. The file
layout is similar to .INI files in the Windows environment. The first line
must contain the string [Warehouse] and subsequent lines are used to set
global options available in the SET statement. Options available are:
AUTOPAD, MSGS, PAGELENGTH, PAGEWIDTH, PROGRESS, START and STATS. Note that
the contents of the Warehouse initialization file will override the intrinsic
default settings for all Warehouse client and server processes that are
executed from its directory. An
example WH.INI file: [Warehouse] Any malformed
or erroneous key/value pair setting will be displayed before the Warehouse
startup banner line. In the event of such an error, Warehouse will continue
to run under the default settings. Tip : the current option settings can be reviewed with the
Warehouse SHOW command. 1> show AUTOPAD : OFF COMMITRATE : 1 MSGS : ON PAGELENGTH : 55 PAGEWIDTH : 80 PROGRESS : 0 START : OFF STATS : ON VERSION : Warehouse 3.00.4800-W - Dec 16 2004
12:37:36 What
happens if during disaster recovery I restore Warehouse to a backup system ? When
activated on the backup system, the Warehouse instance will recognize that it
has been relocated. In order to insure continued processing, the instance
will run for two more weeks under a full-function demonstration license.
Before the two weeks has expired, please call a Taurus Support representative
for production re-validation should the instance need to remain on the backup
system. Why does
Warehouse change its validation state when I change either my MPE/iX
HPSUSANNAME or HPCPUNAME ? Warehouse
will recognize when either of these MPE/iX system parameters have been
changed, and it will assume that either a system upgrade has taken place or
else that it is participating in disaster recovery. In either
case, Warehouse will continue to run for two weeks under a demonstration
license. You will need to call a Taurus support representative for production
re-validation. If the system has been upgraded to a more powerful HP3000
model, depending on the terms of your contract you may need to obtain a new
license from Taurus. How do I
download a copy of the Warehouse Reference Manual ? The Warehouse
Reference Manual is available in PDF format at the Taurus download web
site . Is a
Warehouse script able to access more than one database ? A single
Warehouse script is capable of accessing multiple local and remote input
sources and multiple local and remote output targets. On what
port number does the Warehouse Server listen ? By default,
the Warehouse server listens on port 1610. And by default, a Warehouse
client will attempt to remotely connect to a Warehouse server on that same
port 1610. To configure a different listening port, see the Warehouse Manual.
For Windows platforms, see the Release Notes for Warehouse version 3.00.4500. Is a
Warehouse script required to execute on a particular machine
? No, the
script is not required to execute on a particular machine. The only
requirement of a machine is that Warehouse be installed and running on that
system and that the system have connectivity with systems that house the
script's data sources and targets. These remote systems should have Warehouse
installed and running as well. Utilization
and contention being equal, co-locating script execution with the most
heavily-used data sets can often improve performance by reducing network
latencies. Since scripts can be written in a portable manner, an unchanged
script can be run and performance-evaluated on any system where Warehouse is
present. What’s the
difference between the START and XEQ commands ? When you use
START, the statements are not displayed as they are processed. When you use
XEQ they are displayed. Tip : when debugging your Warehouse scripts, use XEQ. What’s the
difference between a FORMAT and a DEFINE ? A FORMAT is a
template : it establishes a definition that
describes a record or a variable. A FORMAT can be used by subsequent DEFINEs
to allocate actual variables. A FORMAT can also be nested within another
FORMAT. To a C programmer, a FORMAT is equivalent to a struct. What are
the respective effects of the ;NOWAIT (the default)
and ;WAIT directives in the context of a remote COPY command within a READ
loop ?
Let us
consider the following abbreviated example : read row =
localdb.emp In this
example, the COPY statement to a remote database remotedb is NOWAITed
(the default) : Warehouse will continue to process
the script, reading from localdb and inserting toremotedb, without
waiting for a response from the remote Warehouse server for each remote
operation. This is a significant performance optimization as operations are
interleaved. NOWAIT has
implications when used inside a TRY/RECOVER block. Should an error occur
during a NOWAITed COPY, the RECOVER statement would not be entered with the
record that had the error. On the other
hand, if the COPY statement were coded as follows : Warehouse
would wait until the remote COPY operation completed before reading
the next row from the source. Should an error occur during a WAITed COPY,
RECOVER would be entered with the specific record that had the error. Within
a TRY/RECOVER block a COPY statement should be WAITed. Tip : when debugging failures, particularly network failures,
use the ;WAIT option for better fault isolation. What are
the reserved words in Warehouse? Reserved word
lists are published for languages that have a syntax
where identifiers can occur in the same places as keywords. This makes it
illegal to use keywords as an identifier and requires a list of reserved
words or keywords. Warehouse's language was designed not to require reserved
words. Therefore there is no need to publish a list of keywords. To illustrate,
the following syntax is legal but is neither sanctioned nor maintainable
: 1>
define print : string value 'hello' hello
world
Tip : code obfuscation of this kind is extremely poor practice.
Consider, for example, the consequences of performing a change all on a name
that is both a variable identifier and a Warehouse statement, such as print or string. What are
the scope rules for Warehouse variables ? All variables
are considered global to a script unless defined within a function. If a
variable is defined within a function, it is considered local to its defining
function. Global
variables are persistent and can be accessed from anywhere in the script. A
function variable is local to its defining function and as such cannot be
accessed from outside of that function. A function variable is an automatic variable : it is created upon entry to the function and
destroyed upon exit. If a global
variable and a function variable share the same name then any use of that
shared name outside the defining function will reference the global variable.
Any use of that shared name inside the defining function will reference the
local function variable. The following code demonstrates these scope rules,
where the variables i and j are variously defined as both
global and local variables : 1>
define i : integer global i=
13 global j= 17 Tip : unless a variable is truly being used as a temporary work
area - for example, as a simple loop counter - then it is safer
practice to use unique names. Consider the unintended consequence of
accidentally deleting the DEFINE for a local
variable that shares a name with a global variable. Given compatible data
types the script will compile, but the execution results will probably be
unexpected. What is
the scope of the Warehouse comment (*) character ? Unlike some
languages where a comment indicator applies only to the physical line it
occupies, the Warehouse comment character (*) applies to a logical line,
where a logical line is composed of one or more physical lines. Hence the
scope of a Warehouse comment is one or more physical lines. In the
following OPEN statement, the Warehouse continuation character (&) is
used to extend the statement across multiple physical lines : open db
& When
Warehouse encounters a comment character in the script, it looks for trailing
continuation characters, and discards all continued physical lines that
immediately follow. Warehouse then resumes its detailed parsing at the next
un-continued line. Here a single
comment character disables all three physical lines spanned by the OPEN
statement : * open db
& How can I reference
tables or columns with lower-case or mixed-case names ? Warehouse assumes that database object names are
stored in upper-case. In the event that an object name is not stored in
upper-case, then in order to reference this name within a Warehouse script,
you will need to delimit it with braces (curly brackets). The use of braces
instructs Warehouse to work with the database object name as it appears in
the script. For example, let's assume a SQL Server database
that was created with a binary collation, containing the table MixedCase formed by the three columns,
Col1, Col2, and Col3. In this first example, we get a compilation error
referencing the table name MixedCase
because the required braces have not been used : 1> open db odbc
unibin [Microsoft][ODBC SQL
Server Driver][SQL Server]Changed database context to 'UniBin'. (ODBC 01000) [Microsoft][ODBC SQL
Server Driver][SQL Server]Changed language setting to us_english. (ODBC
01000) 2> list db TABLE NAME OWNER TYPE ---------- ----- ---- MixedCase dbo TABLE dtproperties dbo TABLE sysconstraints dbo VIEW syssegments dbo VIEW 3> show db
MixedCase Error getting number
of Result Columns. (WHERR 21037) ODBC error 208:
[Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid object name 'MIXEDCASE'. (ODBC S0002) [Microsoft][ODBC SQL
Server Driver][SQL Server]Statement(s) could not be prepared. (ODBC 37000) Error attempting to
show table '"MIXEDCASE"'. (WHERR 21018) 4> In this second case, the compilation is successful
as we reference the mixed-case names within braces : 1> open db odbc
unibin [Microsoft][ODBC SQL
Server Driver][SQL Server]Changed database context to 'UniBin'. (ODBC 01000) [Microsoft][ODBC SQL
Server Driver][SQL Server]Changed language setting to us_english. (ODBC
01000) 2> show db
{MixedCase} RECORD
// 46 bytes Col1 : ODBC CHAR(10) ALLOW NULLS
OFFSET 1 Col2 : ODBC CHAR(10) ALLOW NULLS
OFFSET 17 Col3 : ODBC CHAR(10) ALLOW NULLS
OFFSET 33 END 3> read x =
db.{MixedCase} order by {Col3} [Using serial read] 4> update x set {Col1} = {Col2} 5> endread 6> How are
transactions handled within Warehouse functions ? Ordinarily
Warehouse by default treats every record read and processed within the
outermost READ loop as a single transaction. However, when
the operation is located within a function Warehouse will not issue an
implicit commit. The Warehouse script must be coded to issue an explicit
commit if this is desired. What is COMMITRATE ? COMMITRATE
determines the frequency with which Warehouse commits transactions.
COMMITRATE is a property that is global to an executing script and an option
that can be altered by means of the SET command. The default
COMMITRATE is 1, meaning that Warehouse will physically commit
every logical transaction, where a logical transaction is defined as all
activity bracketed within the outermost READ loop. If the
COMMITRATE has been set to 0, Warehouse will never issue a commit and it is
the script’s responsibility to issue a commit. And if the COMMITRATE is set
to a value n greater than 1, then Warehouse will physically commit
after every n logical transactions. A transaction
commit prompts the underlying database transaction manager to issue a waited
physical log write to disk to guarantee durability. A larger commit rate is
more performant since there will be fewer waits for log writes. On the other
hand, bigger is not always better : the larger the
COMMITRATE value, the longer shared system resources may be pinned (memory)
or locked (rows), thus preventing other work from proceeding in parallel. What
COMMITRATE is appropriate when using a Message File ? If you are
reading from a Message File, the COMMITRATE should be left to default to
1. The Message
File type has limited transactional capabilities compared to a modern RDBMS.
A COMMITRATE of 1 against the target database will ensure that source and
target are kept in synch in the event of a restart and recovery operation.
Date Handling How do I
compare a date that is in YYYYMMDD character format to today’s date ? Use the
DATE2STR function with $NOW : define
constant_date : char(8) value "20000323" How can I
determine the day of the week for today's date
? Use the
DATE2STR function with $NOW. Sunday is day 1 and Saturday is day 7. 1>
define dt : date the day of
the week for THURSDAY 08-MAR-2001 is 5 How do I
calculate the number of days between today and a supplied constant
? The result of my calculation is wrong. Here’s what I’m doing : Warehouse
provides an implicit DATETIME-to-NUMERIC conversion in the case where the
target variable is a NUMERIC data type such as date1 and date2. In this
example, the Warehouse constant $NOW and the built-in function STR2DATE both
return DATETIME data types that are then converted to NUMERIC by the SETVAR
statements. Consequently
the operation on line 5 is a numeric subtraction, not a date subtraction.
Assuming that today’s date is mid-afternoon July 31, 2000, this operation is
: 20000731.000000
– 200000101.000000 = 630.000000 In order to
correctly calculate the calendar difference, subtract DATETIME data types
from each other. An example : print $now
– str2date("20000101") will print 212 15:32:12 Subtracting
one DATETIME type from another results in an
INTERVAL date type. An INTERVAL type keeps track of fractional days expressed
as time as well as whole days. Warehouse
also provides an implicit INTERVAL-to-NUMERIC conversion in cases where the
target variable is a NUMERIC data type. By converting from an INTERVAL to a
NUMERIC you lose the time precision of the INTERVAL and the result is a
number, in this case 212.000000. Where can
I find the result types of addition and subtraction operations between date operators ? In the
Reference Manual, of course. The following table summarizes that information
:
What is the syntax for setting columns of type Oracle DATE
? Let’s assume
that in an Oracle database with open file handle db there is a column sched
in table foo that has been created as follows : direct db,
"create table foo (event char(20) primary key, sched date)" To variously
populate the rows by means of the compile-time DIRECT statement : direct db,
"insert into foo values (‘vacation_start’, null)" Alternatively,
to populate the rows by means of the run-time COPY after using the Warehouse
SETVAR statement to assign a value to the sched column : setvar
sched = $null What
happens if I neglect to set an element in a
variable defined as a Warehouse DATETIME data type ? The Warehouse
DATETIME data type is a composite data type. Defaulting will occur for any
element that has not been explicitly set. The following code describes
this : 1> 1901-01-01
00:00 The default
values assigned to the Warehouse DATE and the TIME data types are identical
to the default values assigned to DATETIME : 1>
define d : date 1901-01-01
What are
some of the ways that preprocessing can be useful ? The following
example shows a couple practical applications of external and internal
preprocessing variables. We first use
preprocessing to find an external environmental variable name, WH_UTL_DIR,
where the value of the name is a shared directory that contains a file of
commonly-used Warehouse FUNCTIONS and DEFINEs. We then
create two internal preprocessing variables, node and database,
with which to open a database. Should the open fail, we trap the error and
exit the script. * script
cannot run if the environment variable is missing * set
preprocessing variables for the data source # setvar
database = & * open the
data source Why does
my script fail when I access an environmental variable while preprocessing ? The variable TRACE_FLAG is used in
many of my scripts in the following manner : Case
sensitivity is indeed a contributing factor. A test for the condition #
if TRACE_FLAG in the preprocessing environment produces one of four results : true, false, neither, or undefined variable
(WHERR 8271). The WHERR 8284 is generated when the test evaluates to the
third condition, neither. As the
Warehouse Manual explains, the preprocessor variables TRUE and FALSE are not
the same as the script constants $TRUE and $FALSE. The preprocessor expects
that if an environmental variable is going to be tested as a boolean, then
that variable should contain either TRUE or FALSE (uppercase). If the
environmental variable contains anything else, then it will evaluate to
neither true nor false and Warehouse will generate the error you saw. In
order to test an existing environmental variable as a boolean without
incurring an error, try the following : #if
UPS(TRACE_FLAG) = 'TRUE' What
happens if I modify an environmental variable while preprocessing
? The short
answer is that for this script you have modified the contents of the
environmental variable and that any subsequent reference in this script to
that variable name will access the modified contents. For example, assuming
that the environmental variable FOO has been set in the shell to bar
: 1> #
print FOO bar_none If you wish to
retain the original contents of the environmental variable, you should make
use of a second preprocessing variable : 1> #
setvar FOO2 = FOO bar
bar_none How can I
determine what lines of preprocessing code have and haven't been executed ? Examine the
compilation output, since preprocessing occurs during that phase. If the line
of code is flagged with an asterisk, it has not been executed; otherwise it
has been executed. In the
example below, the environment variable WH_UTL_DIR is not initially
defined. So the code within the first if not defined(...
block is executed, setting WH_UTL_DIR. In the compilation output these
lines are not flagged with an asterisk. The second
time the same check is made, the code within the if not defined(... block is skipped. In the compilation
output the lines that have been skipped (7-9) are flagged with an asterisk. 1> # if
not defined(WH_UTL_DIR) c:\mydir How can I code a script to
conditionally compile based on platform type ? Simpler than invoking and parsing an external
system command is to examine the version of the Warehouse client that is compiling
the script. The following script output will give you an idea of what you can
do : d:\>.\wh.exe Warehouse 3.00.3440-W (c) Taurus
Software, Inc. 2004 Installed for: Taurus Software
Inc. 1> print "You are running
${WHVERSION}" 2> print "This version of
Warehouse "; token("${WHVERSION}",2," -"); & 3> " with suffix ";
str("${WHVERSION}",len("${WHVERSION}"), 1); " runs
under "; 4> 5> #IF
str(WHVERSION,len(WHVERSION),1) = "W" 6> print "Windows" 7> #ELSE IF str(WHVERSION,len(WHVERSION),1) = "L" 8* print "Linux" 9* #ELSE 10* print "unrecognized OS suffix
"; str("${WHVERSION}",len("${WHVERSION}"), 1) 11* #ENDIF 12> go You are running Warehouse
3.00.3440-W This version of Warehouse
3.00.3440 with suffix W runs under Windows What is
the difference between the DIRECT and CALL DIRECT statements? DIRECT is
invoked immediately at statement compile time. CALL DIRECT is invoked at
run-time. Both commands allow execution of SQL statements. Common uses
include table creation, truncation, the adding of indices after a load, and
execution of stored procedures. Tip : you can use DIRECT within a script to set up your
environment - to create and populate tables, for example - before the
run-time logic is executed. Can I call
an Oracle stored procedure from within my Warehouse scripts
? And I need to pass it a few values and have it modify a few
values. You can
invoke stored procedures using standard Oracle syntax through the DIRECT or
CALL DIRECT statements, since these statements simply send a string as-is to
the Oracle RDBMS. The string is not required to be static and can be built on
the fly. |