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.6690. 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) J - Sun Solaris x86 L - Red Hat Linux x86 M - Hewlett-Packard 3000 MPE/iX operating system. Q - IBM AS/400 system iSeries V5R4 R - IBM RS6000 system running AIX S - Sun Solaris SPARC 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. Tip : if implementing a temporary table in this manner,
consider a unique naming scheme for the table to avoid name collisions with
other scripts that are doing the same thing. And don't forget to drop the
table when you are done with it.
open db
odbc source_ss7 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 ? The
exclamation mark and equivalent colon commands (‘!’ and ‘:’) are executed
immediately during the script compilation phase, along with invocations of
DIRECT and OPEN statements. The SYSTEM
function is invoked during the execution phase of a script. For example, from
within Windows, to list all files of format to*.txt in the current
working directory, first during compilation, then during
execution : Tip : the Warehouse client is blocked until the spawned child
process returns. How can I
send email from a Warehouse script ? How can I
send a script listing and compile errors to the same file ? MPE/iX ci There is no
support for redirecting output from standard error on Windows 95 or Windows
98 I have a
number of Unix servers each running Warehouse. Is there a way to check on the
status of the WHSERV processes beyond invoking ps via 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
? When a
Warehouse client parses a OPEN ... REMOTE statement while processing a
script, it uses the information contained in that OPEN statement to place a
remote connection request to the Warehouse server listening on the specified
remote machine. For example, The server
then checks the Warehouse authorization file, AUTHFILE, to determine whether
the client computer/user and server/user combination are authorized for
service. If
authorized, the server reads /etc/passwd to check the validity of the user
and password on unixmachine. Finally, if
the user is valid, the server changes the working directory to the directory
listed in /etc/passwd and SETUID's itself to the user id. At this point the
Warehouse can open the database described in the OPEN statement 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 ?
How do I
OPEN an MPE/iX KSAM file ? How can I read a sorted CSV type file ? There are two approaches you can take. First, you can sort
the file externally using a tool like Excel or the platform's sort utility.
Or else you can use a FORMAT statement in the READ ... ORDER BY statement to
have Warehouse return the CSV file in a sorted order. This second approach requires that the FILENAMES attribute
not be used in the CSV OPEN statement. For example, the CSV file below is
ordered by empid and the READ returns rows sorted by lastname
and firstname : 1> 2> open db csv
csvfile.csv mode=r 3> 4> format afmt :
record 1 -> empid : char(6) 7 -> firstname : char(16) 23 -> lastname : char(16) 39 -> end 9> 10> read x = db
format afmt order by lastname, firstname Using serial read 11> print x 12> endread 13> 6 dino flintstone 1 fred flintstone 3 wilma flintstone 5 bambam rubble 2 barney rubble 4 betty rubble How can I
match on nulls ? When I test for equality using the
= operator between variables where both contain null, the test always fails. Note : in 2.07.3360,
Warehouse introduced the == comparison operator. This operator greatly
reduces the complexity of comparing nullable fields. See the answer to What
is the == operator ? in
the Major new Warehouse Features section of this FAQ.
* first match the Oracle row on lastname
What is
the length of a CHAR or a VARCHAR2 variable when its contents are null ? When you use
the LEN function to determine the length of a variable that contains null the
result returned is $null. If you try to assign the result of the LEN function
to a numeric receiving variable, that variable is unchanged. Finally, if you
compare result of the LEN function to another numeric variable, the
comparison will always fail as LEN does not return a number. In order to
establish whether a variable that accepts nulls has a valid length, you need
to first check whether that variable contains null before invoking the LEN
function. 1>
What is
the file named WHLOG ? WHLOG entries
are listed in sequential order. The WHLOG file is not purged out from run to
run. If you want to archive WHLOG files or to free space, simply rename or
purge the existing file before restarting the Warehouse Server. On start-up,
the Warehouse Server will create a new WHLOG. The following
are the WHLOG entries for a typical non-error session. A remote Warehouse
client on an MPE/iX system logs into a WHSERV on Solaris as user homer. Connection
opened from 63.121.20.7 How do I
find WHLOG on an MPE/iX system ? On MPE/iX,
the Warehouse Server is launched as a job. To find it,
issue a SHOWJOB. 3:26 PM
WORK [4]C-> showproc ; job=#j76 How do I
find WHLOG on a Unix system ? On a Unix
system, the WHLOG will be found in the directory where Warehouse has been installed.
By convention, this directory is /usr/local/taurus/whii. If you are not
certain where Warehouse has been installed, the following command will show
you where : sol12% ps
-aef | grep whserv What is
the file named WHVALLOG ? 01-Jul-04 13:35:05 Entered demonstration code:
2B06-EBFE-BA95-43BE-FFF2 01-Jul-04 13:35:05 Set expiration date to 20040715 01-Jul-04 13:35:13 Set return code to 200-126-101-5 01-Jul-04 13:36:02 Entered validation code:
7ABC-12D7-C1C1-0A9A-821F 01-Jul-04 13:36:14 Set production license for: Taurus
Software, Inc How do I
find WHVALLOG ? WHVALLOG can
be found in the same directory as WHLOG, where Warehouse has been installed. 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 can I
find out about the database that I have opened with a Warehouse client ? The specific
information generated by this command differs based on the type of database
or file that is open. 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 ? The –showinfo
parameter will display this information. See the Release Notes for version
3.00.4410 for additional detail. The client syntax is : <warehouse client> -showinfo [output-file] C:\Program
Files\Taurus\Warehouse>wh -showinfo Program version : Warehouse 3.00.4800-W Program name : wh Warehouse client program:
C:\Program Files\Taurus\Warehouse\WH.EXE Warehouse home : C:\Program Files\Taurus\Warehouse Operating
system : MS Windows System ID : 000da5e8ef27 Validation
status : Expires on 06-JAN-2005 System name : WINQA08 User login name : qa Word size : 32 bits (Little endian) Current date and
time : 12-DEC-2004 13:09:24 File system : Static Posix libraries : Unavailable TurboIMAGE :
Unavailable ODBC libraries : Available Oracle : Available (Dynamic) Oracle 8 features :
Available (Dynamic) What can I find out
about a remote Warehouse operating environment ? The
-serverinfo parameter will display environment information for a remote
Warehouse server. See the Release Notes for version 3.00.4410 for additional
detail. The client syntax is : <warehouse client> -serverinfo [server name or ip] The information displayed is the same as the -showinfo information
documented above. Is there
an easy way to initialize a record buffer ? When a
variable is defined as USING a table format, Warehouse will initialize that
variable to the table column defaults. Hence, in the following example, emp_clear
can be used for repeatable initializations of identical structures such as new_emp.
define
new_emp : using db.emp Why do I
lose the remainder when I divide Image I2 amount field by 100
? An Image I2
data type is an integer data type where the decimal point, if any, is
implied. Integer math does not recognize decimals. To generate results with
both a quotient and a remainder, you can use floating point data type. The
following script output describes both the issue and some solutions : 1>
define i : image i2 value 10125 Tip : the TRUNC function is handy when converting the content
of implied decimal data types to an ascii representation with a decimal point
and an explicit remainder length, like money. Note that in version 2.07.3060,
Warehouse introduced the DIVF and DIVI functions to simplify integer and
floating point division : check the Major new
Warehouse Features section of this FAQ to read a description of these
functions. How can I
convert an Image J2 currency amount to a correctly-formatted text string ? An Image J2 data
type, like an Image I2, is an integer
data type with an implied decimal point. To convert this value to a text
string with a decimal point followed by two decimal places, see the following
script output : 1>
define j : image j2 value 9999 Is there a
way of determining how long it takes for the Warehouse engine to compile the
Warehouse script and open the databases ? The mechanism
to gather this measurement is platform-dependent.
One approach that could be taken on Unix is to insert two lines at the
beginning of the script. The first line begins with an exclamation mark and
executes at compile-time. Since it is the first compile-time statement in the
script, it will be the first compile-time statement to be executed. The
second line executes at run-time. Since it is the first run-time statement in
the script, it will be the first run-time statement to be executed. The
difference between the output of the first line and that of the second is the
elapsed time of the compilation phase. !echo
start compile -- `date` start
compile -- Thu Dec 21 08:28:35 PST 2001 How can I
prevent the cleartext password provided in my
database OPEN from printing on the Warehouse script's stdout ? If you place
the OPEN in another file that is invoked by the main script by means of a
START command, the lines in the invoked file will not echo to the stdout. For
example, given that the following database OPEN is located in the file named open.whs
: open db
remote sol14 user=qa password=homer & When the main
script STARTs open.whs, the sensitive password information will not be
made public : 1>
start open.whs Note that in
2.07.3360, Warehouse provides encrypted password capabilities. See the answer
to Does Warehouse support password encryption ? in the new
features section. How can I
stop Warehouse from stripping trailing spaces from fixed-length columns ? This is happening to Image X and U and to Oracle
CHAR data types. The PAD functions allows users to specify that the column be
padded with spaces to its maximum size. The following script provides an
example where the variable v1 is copied without and with the PAD function : Can I create
or access a SQL View from a script ? You can do
both. The following script illustrates the creation of the View empdept
based on a join of the emp and dept tables, and the subsequent
sequential read of that View. The RDBMS used in this example is Oracle. open db
odbc nt05_o816 user=scott password=tiger
Tip : for names that differ due to special character
separators, the MAGICON function provides SETVAR's record-level move
capabilities with name matching extensions. I'm
running GlancePlus/iX on MPE/iX : why is Warehouse's
CPU utilization high ? Since the
only two processes running on your system are the Warehouse client and
GlancePlus/iX, it is not surprising that the Warehouse client uses a high percentage
of the CPU. This is a good thing and not a bad thing :
work is being done quickly. Does
Warehouse have a conditional expression like C's expr1 ?
expr2 : expr3 ?
Sometimes it
is necessary to shut down either the source or the target of data movement.
In order to do this with no loss of transactions, you
should : 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 ? Let's assume that you really don't need a permanent
dataset-resident store of bad TurboImage data. The following Warehouse script
uses the FIELD() function to store bad data into a
set of commonly-used TurboImage data types that are defined as script
variables. These data types are subsequently tested for invalid data. 1> // or10 initialized to the
value 'check\11data' 2> 3> define or10 : oracle raw(10)
value 0x636865636B1164617461 4> print "\nfield or10
contains: "; or10 5> 6> // check for non-ascii in a
character-type variable 7> 8> define x : image x10 9> setvar x = field(or10, 1,
"image x10") 10> if scrub(x, " ")
<> x 11> print "\nfailed : non-ascii data in
x-type field" 12> endif 13> 14> // check for invalid format
in z-type variable 15> 16> define z : image z4 17> setvar z = field(or10, 1,
"image z4") 18> try 19> setvar z = z + 0 20> recover 21> print "\nfailed : invalid zone
decimal content in z-type field" 22> print "\t"; $err.wherrmsg 23> endtry 24> 25> // i-type variable is
unformatted binary and should always succeed 26> 27> define i : image i2 28> setvar i = field(or10, 1,
"image i2") 29> setvar i = i + 0 30> print "\n"; i field or10 contains:
0x636865636B1164617461 failed : non-ascii data in x-type
field failed : invalid zone decimal
content in z-type field Illegal sign in zoned decimal number 1667786083 Why can't
Warehouse access a table that I know exists ? Why do I get
the following loader error when running Warehouse against Oracle 8i on Solaris ? To set the
path for Bourne shell: The Solaris ldd
command lists the dynamic dependencies of executable files. Tip : if the Warehouse client invocation is wrapped in a
customized shell script that manipulates environment settings incorrectly,
then a temporary LD_LIBRARY_PATH could disappear without leaving tangible
evidence of the condition. 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
? Why won't
a backslash print correctly in my script ? The backslash
is used to place certain special characters in a string. Combined with the
special character that follows it, the backslash represents a single
character. For example, new line is represented as \n. In order to represent
the backslash character itself, use a double backslash as in the following
example : 1>
define slash : x1 value '\\' slash:\ Why
doesn't a script created with a Windows editor execute correctly on the HP3000 ? The problem
you have encountered is the traditional cross-platform issue of text file
line termination. There is no common standard for the end-of-line indicator : line termination is represented differently on
different platforms. Many modern file transfer programs will convert text
file line terminators automatically, but some will not. Warehouse
uses the standard library fgets function to read a script, and that function
is adapted to a specific platform. On MPE/iX, fgets uses the record size. On
Unix, fgets looks for a Line Feed; on Windows, it looks for Carriage
Return/Line Feed ; and on the Mac OS, Carriage
Return. In this case,
when you moved the text file from your PC to the HP3000, the end of line
indicator was not converted from a Carriage Return/Line Feed to the format
recognized by MPE/iX . Why do I
get an error when I try to open up an MPE/iX
Message file with mode=v ? It returns the following message : Unable to open file 'msg.pub.prd'
using mode 'r+b': If you are
using ASCII capture files, the mode to use is mode=v. If instead you
are using an actual MPE/iX Message file, the mode must be mode=r ndr. You
were trying to open an actual Message file with a mode designated for an
ASCII capture file, hence the error message. 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 ? See the
answer to the earlier question "Why can't I remotely access SQL Server
on an NT4.0 machine ?" . If you look
at the WHLOG on the server machine, you will find an indication of the problem : user qa is the culprit. Jun-14
11:58:36 Login failed - Why do I
get a "table does not exist" error after I've just created it ? 5>
endread Why can't
I demo validate my Warehouse instance on MPE/iX ? I
just received the code, my system date is correct, but I'm seeing a message
telling me that the validation code I entered has expired. The MPE/iX CI
SHOWTIME command displays the system time. On the other hand, the MPE/iX CI
SHOWCLOCK command displays the system time zone setting as well. In this case,
the time zone setting reads : TIME ZONE:
137122 HOURS 15 MINUTES WESTERN HEMISPHERE. Warehouse is
time zone aware. Once the erroneous time zone setting has been corrected with
the SETCLOCK command, the instance will validate. Why do I
get a networking error when I OPEN a remote database ? Warehouse
relies on system and network services in order to operate. When experiencing
a networking error, the following diagnostic steps should be taken : Determine
whether basic network connectivity exists between systems :
ping the remote system by IP number. Demonstrate
DNS resolution : ping the remote system by
fully-qualified name. Ensure that
there is no firewall on the remote site denying access to port 1610. Ensure that
no other application has port 1610 already open. Check that
the Warehouse server is running on the remote system. Ensure that
the Warehouse software on the remote system is not running with a lapsed demo
code. Demonstrate
simple application loop-back : run the Warehouse
client on the database system with the -c option. Demonstrate
application loop-back : run the Warehouse client on the database system
and OPEN the database Demonstrate
simple application connectivity : run the Warehouse
client remotely with the -c option. Determine
whether the AUTHFILE settings on the remote server system are correct,
particularly if the IP addresses or domain/subdomain identities have been
changed. If this final
step fails, there is likely a system security or permissions issue. Why do I
see a WHERR 20001 when connecting remotely ? This WHERR
indicates that you are having problems logging on to the remote system. If the remote
system is MPE/iX, the error message will be one of : Why do I
get a LDERR 505 when starting WHSERV on the HP3000 ? For a program
to run the group needs all of the capabilities required by the program. The
Warehouse server program requires the following capabilities: BA, IA, PM, MR,
PH. To fix this log on as MANAGER.SYS and enter the following two commands: Why do I
get a WHERR 18002 when opening an Oracle data base ? You need to
specify a user/password to connect to the instance, for
example : Why do I
get a WHERR 21037 when reading a SQL Server database defined with a binary collation ? When you turn
on binary collation (for example, Latin1_General_BIN), the database becomes
case-sensitive. So if your table/field names are not all uppercase, you must
enclose them in curly braces. The following script works fine: Why do I
get a WHERR 6006 (Illegal sign in packed decimal number) ? The Warehouse
runtime error is telling you that a TurboIMAGE P-type source field does not
contain a legal sign. The last nibble of a P-type field should be 0xF
(unsigned), 0xD (negative), or 0xC (positive). When Warehouse sees none of
these values it throws a WHERR 6006. It sounds like you have bad data in that
field. Tip : you can wrap the assignment in a try/recover block in
order to retain control should Warehouse throw an error. 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 ? The full
pop-up error reads : "An error occurred during
the move data process : -623". This error
indicates that the installer was not able to overlay an existing file because
that file was in use. You were probably trying to install a release of
Warehouse in a directory while one or more existing Warehouse executables
were still running from that directory. The existing
Warehouse instance should be quiesced before the installation overlays its
files. Stop the Warehouse service and any local Warehouse clients, and wait
until any and all Warehouse servers have terminated before re-installing. Why do I
get an MPE/iX FSERR 151 when reading from a Message File ? Although the
following approach is not guaranteed to always resolve the problem, it is one
that Quest Software suggests as way of addressing off-hour emergencies
associated with this condition. In the following example, the Message File is
named LTMX.DATA.NETBASE. Why do I
get a logon_cmd error when OPENing an MPE/iX database ? The OPEN
syntax you coded : The correct
syntax is : Why do I
get an ORA-12545 when OPENing an ORACLE database ? In this first example, ORACLE_HOME (and ORACLE_SID) is set
correctly, and the OPEN succeeds : 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
?
What
MPE/iX JCWs are set on a failed OPEN ? The following indicators are set :
CIERROR, JCW, and CJCW. This one-command script fails on the OPEN statement
and sets these indicators. PUB.QA [10]: RUN WH.WHII.TAURUS; INFO='BADOPEN' Warehouse 3.00.3100-M (c) Taurus Software, Inc. 2004 Installed for: Taurus Software, Inc. 1> open db remote nt08.taurus.com & 2> user=qa
pass=homer & 3> odbc
nosuchdsn Finding nt08.taurus.com... Trying 67.37.146.18 Connected to: Warehouse 3.00.3100-W on nt08 Unable to open ODBC data source. (WHERR 21026) ODBC error -1: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (ODBC IM002) Error(s) in script.
No script processing performed. (WHERR 8006) run wh.whii.inhouse;info='BADOPEN' Program terminated in an error state. (CIERR 976) PUB.QA [11]:SHOWJCW CIERROR = 976 JCW = FATAL0 CJCW = 1 PUB.QA [12]: This allows the following logic to be appended to a job to
test for success or failure : !if cierror <> 0 or cjcw <> 0 ! tellop Job EC001 Failed.
! tell
mgr.qa;***********EC001 FAILED*************** !else ! tell
mgr.qa;*************EC001 complete************** !endif ! !eoj How can
I fix a Warehouse client's "Out of new memory" condition on MPE ? Let's assume that you are building a very large script and
suddenly your testing fails with the following : Out of
new memory (New 95300). Number
of mallocs=4133465 ----
mallinfo() ---- arena =
81692656 ordblks
= 634 smblks =
1000 hblkhd =
280 usmblks
= 9816 fsmblks
= 17384 uordblks
= 81496824 fordblks
= 168352 keepcost
= 3480 ****
Data memory protection trap (TRAPS 68). ABORT:
WH.WHII.TAURUS NM PROG
89.000be288 out_of_memory+$40 Program
terminated in an error state. (CIERR 976) REMAINDER
OF JOB FLUSHED. "Out of memory" means the client program ran out
of memory. This error is correctable by running Warehouse with a bigger
NMHEAP : RUN WH.WHII.TAURUS;NMHEAP=200000000 The system default NMHEAP value is set in SYSGEN. MISC
-> SHOW shows the current value and MISC -> STACK sets the current
value. The values on our development machine (which have never
been changed) are: STACK command MAX MIN CURRENT ----------------- ---------- ---------- ---------- DEFAULT NM STACK 749998080
262144 393216 MAXIMUM NM STACK 749998080
262144 749998080 DEFAULT CM STACK 4096
256 1200 MAXIMUM CM STACK 31232
256 31232 DEFAULT HEAP
1047527424 524288 81920000 MAXIMUM NM HEAP
1047527424 524288 1047527424 What
does "Error dynamically loading getspwnam()"
mean ? Warehouse server error: Error dynamically loading getspwnam() (WHERR
20001) I am running on an HP-UX 11 64-bit platform
configured as a Trusted System. This
condition is caused by a PA_RISC library version incompatibility. Please see What does the Warehouse server require on
an HP-UX Trusted System ?
What does Invalid client program. (WHERR
20001) mean ? This message is telling
us that a Warehouse Client of version 2.07 or earlier is failing to connect
to a Warehouse Server that is version 2.08 or later. Due to a significant
enhancement in the Warehouse security layer between 2.07 and 2.08, a 2.07 or
earlier Warehouse Client cannot connect to a later release of the Warehouse
server. You will need to upgrade the 2.07 software. This is a good idea in principle : the first 2.08 release, 2.08.0100, came out in
August 2003. Why do I get the message Warehouse validation error number
20 ? This error is emitted by
the Warehouse Client or Server if unable to open the message files in the
Warehouse directory. The likely reason for this condition is either that the
Warehouse directory has been damaged or moved, or else that the Client or
Server has been executed from a soft link - for example, a Unix symbolic
link. To correct the problem
caused by the soft link, please direct Warehouse to its directory by setting
the WHHOME variable in the environment of the user running the process.
WHHOME should be set to the fully qualified Warehouse directory. Both
Warehouse Client and Server check for the existence of this variable : if it is present, they will use the value to
locate the Warehouse home directory. For example, using the Bourne shell : WHHOME=/usr/local/taurus/whii/@ export WHHOME What does Decimal number overflow (WHWARN
5267) mean ? Warehouse is
informing you that the receiving field is not large enough to hold the
numeric result of the right-side expression, variable, or literal. In such a
case Warehouse will issue a warning, set the receiving field to 0, and
continue processing. If the result is a valid one, then consider either
expanding the receiving field or changing the field's data type. There are a number of
other numeric overflow-related WHWARN messages that are generated for the
same reason, varying only with the data type of the receiving field. These
include : Numeric overflow (WHWARN
6010) Packed decimal field overflow
(WHWARN 6004) Zoned decimal field
overflow (WHWARN 6007) Oracle number out of range. (WHWARN 6019)
Can I read Oracle V$
views from within Warehouse ? 1> open db oracle
system/homer 2> show db
v$sgastat RECORD
// 70 bytes POOL : ORACLE VARCHAR2_(11) ALLOW
NULLS OFFSET 1 NAME : ORACLE VARCHAR2_(26) ALLOW
NULLS OFFSET 17 BYTES : ORACLE NUMBER ALLOW NULLS
OFFSET 47 END 3> read x =
db.v$sgastat for name = "free memory" 4> print x 5> endread 6> go shared pool free memory 44487248.000000 large pool free memory 8388608.000000 java pool free memory 33554432.000000 Database Name Dataset Name Reads Writes
Updates Deletes system/homer V$SGASTAT 3 0 0 0 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 ? For writing
to a FIFO named fifo.dat, the OPEN is : Tip : a FIFO can be created within a Warehouse script at
compile time with a system command : !mknod fifo.dat p . Once the job
is finished, he FIFO can be removed with the standard rm
command. Can I use
the Oracle bulk loader with Warehouse on Unix to insert to an empty table ? When
performance is a requirement, the Oracle SQL Loader (sqlldr) can be directed
to read from a Unix FIFO that a Warehouse script is simultaneously
populating. sqlldr blocks its input and then
performs bulk inserts to the target table. Tip : Table indices can be rebuilt when the load has completed.
Can I
invoke the Microsoft Bulk Copy Program from a Warehouse script
? The Microsoft
Bulk Copy Program (BCP) is a utility for copying large amounts of data into
or out of SQL Server. Please consult the MSDN Library for product
details. The following
is an example of a very simple use of SQL Server bulk insertion. BCP is
invoked by means of the Warehouse DIRECT or CALL DIRECT commands. The source
is a text file that uses the BCP default field separator - the tab, not the
comma. The text file is named d:\src\source and contains two fields
per line and the following three lines : How do I
copy MPE/iX Capture Files using Warehouse ? What is
the default TurboIMAGE locking and how do I obtain more concurrency
? By default,
Warehouse will lock at the TurboIMAGE dataset level. To alter the locking
option, use the SET <tag> LOCKING statement. The SET
<tag> LOCKING MANUAL statement enables manual locking and increased
concurrency. By selecting this option you are then responsible for issuing
LOCK and UNLOCK statements at appropriate points in the processing. It is
useful to develop a systematic locking strategy for all scripts that access
datasets in this manner.
shell/iX>
/TAURUS/WHII/WH Will
Warehouse client 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 ? This option
will run SQL Server threads at a higher priority class than processes or
threads in a lower class. If these lower-priority processes are Warehouse
processes, particularly on a single processor system under load, it is likely
that these Warehouse processes will be starved and that the anticipated
transaction throughput gains will not be achieved. Why does my script run slower against a SQL
table once I have completed an initial load ? All modern SQL optimizers develop query access plans using cost-based strategies. When building a plan, a cost-based optimizer is strongly influenced by table row count. Unless the table's statistics have been updated following the initial load, the row count is likely to be a lesser number than the actual. This discrepancy will lead the optimizer to selecting an inefficient plan (e.g. full-file scan) as opposed to an optimal one (use available index). It is important to update table statistics after any operation that significantly changes a table's row count. What
does the Warehouse Server require to run on an HP-UX Trusted System ? If an HP-UX
system has been configured or upgraded to a Trusted System, the Warehouse Server
is required to perform a different login sequence on behalf of the remote
connection. In order to
log in to a Trusted System, the Warehouse server expects one or two
environmental parameters to be available depending on whether the platform is
PA_RISC1.1 or PA_RISC2.0. These parameters are best set in the profile of the
user nohup'ing the whserv process (root). First, and in
both cases, the Warehouse server must be informed of the existence of the
Trusted environment : export
WHTRUST=1 Second, if
the platform is PA_RISC2.0 and the Warehouse Server is from the 32-bit
Warehouse HP-UX release (suffix H), then Warehouse Server must dynamically
load an alternate PA_RISC1.1 library in /usr/bin rather than relying on the
default PA_RISC2.0 libsec.2 library. This is done by means of the following
environmental parameter : export
WHSECLIB=libsec.1 Note that the
Warehouse 64-bit HP-UX release (suffix F) does not require WHSECLIB to be
set. Can
Warehouse make use of Third Party Indexing ? If a Third
Party Indexing (TPI) product has been installed and enabled, Warehouse can
take advantage of it. 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 ? Warehouse
does not know what kind of TPI optimizations have been externally defined, so
it cannot know whether a particular READ...FOR is going to result in the use
of a TPI index. In the case you describe, Warehouse only knows that given the
non-key predicate fields, the read is serial. Can you
help with the following DBFIND error that I get when accessing a TurboIMAGE database ? Why do I
get an error when I try to READ...FOR using a key that I've defined in my TPI
environment ? Depending on
what results you wish from your query and the capabilities of the TPI product
that you are using, you should chose one or more of the relevant TurboIMAGE
fields for the READ...FOR statement. Once again, you may want to check
the product documentation from your TPI vendor. Why do I
get a WHERR 8007 when I use an ESCAPE statement ? If an ESCAPE
statement is not caught by a RECOVER section, the script will terminate after
printing the line number on which the ESCAPE was invoked, along with a WHERR
8007. The WHERR is informational, since an ESCAPE implies that an unexpected
condition was encountered in the script. If you wrap the script's procedural
code in a TRY/RECOVER block where the RECOVER section is empty, the WHERR
8007 will be masked since the ESCAPE will be caught. Tip : an ESCAPE statement should not be used for standard
program control flow, but to handle exceptional situations and events. How can I exit prematurely from a script ? An ESCAPE
statement outside of a TRY/RECOVER block causes Warehouse to stop processing the
script at run-time. On the other hand, an EXIT statement immediately exits
WAREHOUSE during the compilation phase. So if your intention is to exit
prematurely during run-time, use the ESCAPE statement. An ESCAPE
statement placed within the TRY section of a TRY/RECOVER block will be caught
by that block's RECOVER statement. The
Warehouse code in the RECOVER may then decide either to handle the error
condition and continue processing or else to pass control upward by means of
another ESCAPE statement. If the
Warehouse code decides to issue a second ESCAPE from within the RECOVER
section and this second ESCAPE is nested
within another enclosing TRY/RECOVER block, then the higher-level RECOVER
section will catch this ESCAPE. Otherwise, if
the second ESCAPE is not enclosed by another TRY/RECOVER block, Warehouse
will display both the escape message and an error message and stop processing
the script. The following
example illustrates the behavior of ESCAPE statements within nested
TRY/RECOVER blocks, and shows how the escape message text is accessible in
the $ERR.ESCMSG Warehouse variable. The script begins logical execution on
line 4, with the second RECOVER beginning at line
11. Line 12 is the location of the final ESCAPE. 1> try in 1st
recover : escape 1 Why won't
my script branch to RECOVER on a COPY error ? The ERRORS TO directive takes precedence over the RECOVER.
You should choose one error-handling option or the other. Major new Warehouse features roll-up as of release 3.00.3440 What is
the DESCRIBE statement and how might I use it ?
(2.07.2270)
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: 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-date defined as an X6
that contains a CHRONOS date, you could print the date using the following
Warehouse script: read x =
imdb.ord If you needed
to update ord-date to the current date and time, you could use the
following script: define
date : chronos The default
settings for the CHRONOS fields are as follows : 1>
define c : chronos 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: 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: 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. /usr/local/taurus/whii/CHARMAPS 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 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 For
additional information on CMAP, please reference the 2.07.2780 Release Notes.
To remove all
binary characters from a string use "" for the replacement string
:
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.
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 : How do I
assign a null value from within the IF(,,) function
? (2.07.3060) Can I
initialize a NULLable variable in a DEFINE to null ?
(2.07.3060) 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: Does
Warehouse support the SQL Server UNIQUEIDENTIFIER data type
? (2.07.3060)
SKIP is only
supported for ODBC targets. Does
Warehouse support C++ type in-line comments "//"
? (2.07.3060)
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) 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
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.
TRY(expression, error-result)
ORACLE
<user> EPASS1=<encrypted db password> <oracle parms>
ESCAPE
returns a string to satisfy internal requirements that a function must What is
the new CLOSE statement ? (2.07.3360) where <dbtag> is tag of the database or file to be
closed. What is
the == operator ? (2.07.3360) 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 : 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 :
What are
the IMAGE database limits that Warehouse supports ?
(2.07.3530) Limits have
been increased for IMAGE databases. The new limits are: What are
the size limits for IMAGE P and Z fields ?
(2.08.0010)
What is
the NNOT operator ? (2.08.0090) What is
the TOKEN function ? (2.08.0090) if A =
'one;two,"three,four";five, six , seven,'
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: 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.) 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. See Release Notes for
new Warehouse Features subsequent to release 3.00.3440. Which ODBC
sources can function as a Studio Repository ? The following
databases have been certified to function as a repository for Studio metadata : Microsoft SQL Server, Microsoft Access, and
Oracle 8i or later. What is
the recommended ODBC data source type for a Studio Repository
?
How then
do I obtain exclusive access to a Studio Repository ? If you are
using Microsoft Access for your Studio Repository, you can obtain exclusive
access to the Access database in one of two ways. The first way
is at the ODBC level through the ODBC Data Source Administrator panel : select the Repository's DSN,
Configure->Options, and then check the Exclusive box. The second
way is directly through the Access application :
having opened the Repository's database, enter Tools->Options->Advanced
and click the Exclusive radio button in the Default Open Mode section. If you are
using Microsoft SQL Server for your Studio Repository, you can obtain
exclusive access through the Enterprise Manager :
select the database that you wish to restrict, then right-click
Properties->Options and finally click the Restrict Access check-box
followed by the Single User radio button. Why do ODBC CHAR data types contain an extra
byte when used in FORMATs ? The ODBC char
type adds another byte for a null terminator as required by the ODBC
API. If you don't want that, use an
ORACLE CHAR, TEXT, or an IMAGE X in your FORMAT for fixed character type
definitions that don't require null terminators. Revised: August 7, 2009
|
|