Miscellany
Is a Warehouse script able to read keyboard input?
The ACCEPT function reads a string from standard input. If desired, you can then use other Warehouse functions to further parse and convert the contents of the string.
The ACCEPT is a platform-dependent function.
What is the file named WHLOG?
WHLOG is the local file used by the Warehouse server (WHSERV) processes to log connections, disconnections, and errors. It can be found in the same directory as the WHSERV executable. WHLOG is very useful in both diagnostic investigations and as a means of tracking client access.
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
Mar-12 21:17:18 Login –
as user homer from user MGR.HOMER on mpe12.taurus.com (63.121.20.7)
Mar-12 21:19:21 Connection from 63.121.20.7 closed.
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:25 PM WORK [2]C-> showjob
JOBNUM STATE IPRI JIN JLIST INTRODUCED JOB NAME
#J38 EXEC 10S LP MON 8:05P JINETD,MANAGER.SYS
#J76 EXEC 10S LP TUE 7:40A JWHSERVR,MANAGER.SYS
#S391 EXEC 3 3 THU 3:25P MANAGER.SYS
3 JOBS:
0 INTRO
0 WAIT; INCL 0 DEFERRED
3 EXEC; INCL 1 SESSIONS
0 SUSP
JOBFENCE= 6; JLIMIT= 6; SLIMIT= 10
You can see from the SHOWJOB that the JWHSERVR was launched by MANAGER.SYS and is currently running. That means that you should be able to find the WHLOG file using LISTF.
3:25 PM WORK [3]C-> listf whlog.@.@,1
ACCOUNT= SYS GROUP= WORK
FILENAME CODE ————LOGICAL RECORD——-
SIZE TYP EOF LIMIT
WHLOG * 1B BA 1825356 2147483647
If multiple WHLOG files exist, the one with the * after the filename is the file currently in use by the Warehouse Server. To display the WHLOG file, you may use the PRINT command. To identify all spawned Warehouse servers that may have the WHLOG file open, issue a SHOWPROCwith the JWHSERVR job number as an argument, in this case:
3:26 PM WORK [4]C-> showproc ; job=#j76
QPRI CPUTIME STATE JOBNUM PIN (PROGRAM) STEP
C152 0:01.059 WAIT J76 59 :RUN WHSERV.PUB.SYS
C152 00:17:05 WAIT J76 71 (WHSERV.PUB.SYS)
C200 0:02.329 WAIT J76 76 (WHSERV.PUB.SYS)
C200 0:00.951 WAIT J76 73 (WHSERV.PUB.SYS)
C200 0:00.219 WAIT J76 50 (WHSERV.PUB.SYS)
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
root 29094 1 0 Mar 13? 0:00 /users/homer/inhouse/whserv
In this case, the current WHLOG can be found in /users/homer/inhouse.
What is the file named WHVALLOG?
WHVALLOG is the local file used by the Warehouse to log all validation-related activity. It is created by all post-2.07 versions of Warehouse. The following entries describe a demonstration validation immediately followed by a production validation:
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?
The Warehouse parameter –p provides this capability.
Is there a way to write $ERR (within RECOVER) to a file?
This snippet that logs an error to a simple fixed-length ASCII file is one of many approaches that can be used:
create elog text errlog
define error_record: record
warehouse_error: x4
database_error: x4
error_type: x16
warehouse_error_message: x36
database_error_message: x36
escape_message: x36
script_message: x36
end
.
.
recover
setvar error_record.script_message = “on insert of child record”
setvar error_record.warehouse_error = $err.wherrno
setvar error_record.database_error = $err.dberrno
setvar error_record.error_type = $err.errtype
setvar error_record.warehouse_error_message = $err.wherrmsg
setvar error_record.database_error_message = $err.dberrmsg
setvar error_record.escape_message = $err.escmsg
copy error_record to elog
endtry
What additional details might I want to know about $ERR?
First, you’ll want to know that $ERR is volatile. This means that some or all of the $ERR fields are over-written by Warehouse after every new error.
The wherrno field contains the Warehouse error number of the most recent error. If a subsequent operation succeeds, Warehouse does not reset wherrno to zero. This behavior is similar to the way the c runtime errno is managed. The contents of wherrno should only be examined after an error hasoccurred: for example, in the RECOVER section of a TRY/RECOVER block.
The fields of the $ERR system variable can be altered from within a script in the same manner as a user-defined variable. The $ERR.WHERRNO field can be re-initialized with the following command if the programmer should find it necessary to do so:
SETVAR $ERR.WHERRNO = 0
Below you will find an example of two similar errors, both Primary Key violations on tables named fred, the first against an ODBC data source, the second against an Oracle data source. The dberrnocontains the error code returned from the interface layer, ODBC and OCI respectively.
wherrno……21050
dberrno……2627
errtype……ODBC
wherrmsg…..Error attempting to execute ODBC statement.
dberrmsg…..[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint ‘PK_fred’. Cannot insert duplicate key in object ‘fred’. (ODBC 23000) [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (ODBC 01000)
wherrno……18034
dberrno………-9
errtype……ORACLE
wherrmsg…..Error executing INSERT on table FRED.
dberrmsg…..ORA-00001: unique constraint (QA.FREDX) violated
Every field of the $ERR system variable is not necessarily modified when Warehouse stores new error information. For example, if a non-database related error occurs immediately after a database-related error, the fields $ERR.DBERRNO and $ERR.DBERRMSG will still contain the residue of the previous store. Always check the $ERR.ERRTYPE field first, as this field indicates the most recent error type and, by inference, which of the fields in $ERR are current information.
What can I find out about the database that I have opened with a Warehouse client?
The command LIST enumerates by name all tables that the user can access.
4> list db
TABLE NAME
———-
SCOTT.EMP
SCOTT.DEPT
SCOTT.BONUS
SCOTT.ORD
SCOTT.CUSTOMER
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?
The command SHOW
lists the columns and column properties for a specific table.
5> show db scott.dept
Remote database on sol21c
RECORD (60 bytes)
DEPTNO: ORACLE NUMBER(2) ALLOW NULLS OFFSET 1
DNAME: ORACLE VARCHAR2(14) ALLOW NULLS OFFSET 25
LOC: ORACLE VARCHAR2(13) ALLOW NULLS OFFSET 43
END
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 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:
-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:
-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
define emp_clear: using db.emp
setvar new_emp = emp_clear
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
2> define fp: odbc double precision value 10125
3> print i / 100
4> print fp / 100.00
5> print convert(i,”odbc double precision”)/100.00
101
101.250000
101.250000
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
2> define s: string
3> setvar s = trunc ( ( convert( j , “oracle number”) / 100 ), 2)
4> print s
99.99
Is there a way of determining how long it takes for the Warehouse engine to compile the Warehouse script and open the databases?
What you are asking to measure is the elapsed time required by the Warehouse engine to compile a script, a phase that also includes opening both local and remote data sources and targets and preparing the SQL statements.
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`
call system(“echo end compile —- `date`”)
start compile — Thu Dec 21 08:28:35 PST 2001
end compile —- Thu Dec 21 08:28:38 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 &
oracle scott/tiger home=/u01/app/oracle/product/8.1.5 sid=ora8
When the main script STARTs open.whs, the sensitive password information will not be made public:
1> start open.whs
2>
3> read x = db.emp
[Using serial read]
4>
5> endread
Database Name
Dataset Name Reads Writes Updates Deletes
sol14
EMP 14 0 0 0
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:
1> define v1: image x12 value “1”
2> define v2: image x3 value “234”
3>
4> define n1: image x15
5> define n2: image x15
6>
7> setvar n1 = v1 || v2
8> setvar n2 = pad(v1) || v2
9> print n1
10> print n2
1234
1 234
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
direct db, “drop view empdept”; ignore errors
direct db, “create view empdept as select ename, dname from emp, dept where emp.deptno = dept.deptno”
read x = db.empdept
print x
endread
call direct (db, “drop view empdept”)
Can I use a record-level move instead of moving each field one at a time?
The SETVAR command allows both field-level and record-level moves. In a record-level move, Warehouse moves source field to target field based on name matching. An informational message will be displayed on the standard list if either a name match fails or if field names do match but Warehouse needs to perform a conversion.
In the following example are cases of matches that require and don’t require conversions, and of non-matching fields:
1> format input_format: record
1 -> matching: integer
21 -> diff_length: oracle varchar2(14)
37 -> only_inrec: allbase double precision
45 -> end
6> define inrec: format input_format
7>
8> format output_format: record
1 -> matching: integer
21 -> diff_length: oracle varchar2(10)
33 -> only_outrec: string
45 -> end
13> define outrec: format output_format
14>
15> setvar outrec = inrec
Field DIFF_LENGTH converted from type ORACLE VARCHAR2(14) to type ORACLE VARCHAR2(10)
Can’t find field ONLY_INREC in output record.
Can’t find field ONLY_OUTREC in input record.
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/ 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.
Processes are greedy and unless they are waiting on an unavailable or slow resource, they will compete for the CPU. If, as in your case, there is little competition for the CPU, a process will likely monopolize it.
On a busy system where processes are competing for the CPU, the Warehouse client’s utilization will be lower, particularly if the client is being run as a lower-priority MPE/iX job as opposed to being run as an interactive user.
Does Warehouse have a conditional expression like C’s expr1? expr2: expr3?
Warehouse has an equivalent conditional expression. The following code snippet illustrates it:
1> define FirstName: string
2> define LastName: string value “Cassidy”
3>
4> setvar FirstName = if ( ( LastName = ”Cassidy’ ), (‘Butch’), (”Sundance’) )
5>
6> print FirstName, LastName
Butch Cassidy
How do I perform an orderly shutdown of Bridgeware?
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:
– stop the Export process. This will cause the Writer to terminate.
– allow the remaining Message File entries to drain through the Warehouse client reader.
– when there are no more transactions to process, stop the Warehouse client.
To restart the system:
– start the Export process.
– start the Warehouse client.
How can I look at the raw bits in a field?
Legacy records can contain column data that is inappropriate for the defined data type. In order to examine this value, use the FIELD function, defining the data type of the expression after extraction as either ORACLE RAW or ALLBASE BINARY. A simple example that uses a character field follows:
1> define x: x10 value “ab\ncd\nef”
2> print field(x, 1, “oracle raw(10)”)
3> print field(x, 1, “allbase binary(10)”)
0x61620A63640A65662020
0x61620A63640A65662020
Note that the result is printed in hex, with the \n line termination character displayed as a hex 0A and the field padded with trailing spaces (hex 20). You need to know the byte length of your source field is (in this case ten bytes) in order to ensure that the extraction expression is equivalently defined.
More frequently, the bad data problem is associated with a numeric data type. In the excerpted example below, the Z2 type field VAR-LEVEL from a TurboIMAGE data set is moved to the output column out.var_level within a try/recover block. If the move were to fail due to the contents of VAR-LEVEL, the recover section would set a boolean flag and print out the VAR-LEVEL field contents using the FIELD function. Following the code snippet is the error message that would be generated. This record fails the test because the last byte of a Z type field cannot be space.
try
setvar out.var_level = VAR-LEVEL
recover
print ” Illegal numeric type Z2: VAR-LEVEL”, field(variable-level,1, “oracle raw(2)”)
setvar valid-record = $false
endtry
Illegal numeric type Z2: VAR-LEVEL 0x4E20
Is there a SQL LIKE predicate selection operator for a Warehouse READ?
The MATCH() function will provide what you want.
To list all rows from the Oracle demo database’s EMP table where the EMP.JOB begins with “CL”, you can use the Warehouse MATCH() function to express the string matching you require. For example:
1> open db oracle scott/tiger
2> read x = db.emp for match(job,”CL*”)
[Using serial read]
3> print x
4> endread
7369 SMITH CLERK 7902 17-DEC-1980 800.00 $NULL 20
7876 ADAMS CLERK 7788 23-MAY-1987 1100.00 $NULL 20
7900 JAMES CLERK 7698 03-DEC-1981 950.00 $NULL 30
7934 MILLER CLERK 7782 23-JAN-1982 1300.00 $NULL 10
The MATCH() function recognizes other special pattern characters. See the Warehouse Manual for details.
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