Platform – Specific

 

Can I read Oracle V$ views from within Warehouse?

Given that the user has the required permissions, the dynamic performance V$ views – and all other views – can be accessed with the standard Warehouse READ statement. For example:

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?

Warehouse provides an intrinsic that extends the existing Warehouse COPY statement. This intrinsic allows either the current or the next Sequence value to be moved to a column in the row that is being copied. For example, setting foo.seqnumber to values associated with the Sequence cust_seq. Note that cust_seq is installed as part of the Oracle demo database.

* to obtain next sequence value
read row =localdb.emp
setvar foo_rec.ename = ename
copy foo_rec to db.foo (seq seqnumber = cust_seq.nextval) ; wait
endread

* to obtain current sequence value
read row = localdb.emp
setvar foo_rec.ename = ename
copy foo_rec to db.foo (seq seqnumber = cust_seq.currval) ; wait
endread

If access to the sequence value is required outside the COPY statement, the Oracle system view DBA_SEQUENCES stores the CURRVAL for every database sequence – the column name is DBA_SEQUENCES.LAST_NUMBER. This view can be read like any other view using the Warehouse READ command. If for reasons of strict concurrency a unique sequence number is required per script, then a Sequence can be created within the script using either the DIRECT or the CALL DIRECT commands.

 

Can I read to or write from a Unix FIFO (named pipe) in a Warehouse script?

You can do either. In either case, the Unix FIFO may be opened as a TEXT-type file. As with any file, use the COPY command to enqueue to the FIFO and the READ command to de-queue from it. Do not neglect  to use the FORMAT qualifier in the READ command.

For writing to a FIFO named fifo.dat, the OPEN is:
    open out text fifo.dat mode=w

For reading it is:
    open in text fifo.dat mode=r

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:

abcdef<tab>120
abcdfe<tab>121
abcedf<tab>122

The SQL Server table target is also defined as having two columns. The Warehouse script creates the target table in SQL Server, uses BCP’s bulk load facility to populate the target from the source, and then reads the target to count the number of rows that have been loaded.

open db odbc w12a_ss7
direct db, ‘drop table target’; ignore errors
direct db, ‘create table target (id char(6), val integer)’
define count: image i2 value 0
call direct(db, “bulk insert target from ‘d:\\src\\source’ with (firstrow=1) “)
read x = db.target
setvar count = count + 1
endread
print ‘\nbulk insert count: ‘; count

 

How do I copy MPE/iX Capture Files using Warehouse?

Copying Capture File records with Warehouse is tricky because of the MPE/iX file system.

The output file should be built first with variable length records and the modes for both the input and output files should have a “v” in front to indicate variable length record access. For example:

Your MPE/iX BUILD statement should look like this:
BUILD CAPTOUT;REC=-8960,,V,ASCII 

Your Warehouse OPEN statement to read the source Capture File should look like this:
OPEN CAPTIN FIXED CAPTIN MODE=vr

Your Warehouse OPEN statement to write to the target Capture File should look like this:
OPEN CAPTOUT FIXED CAPTOUT MODE=vw 

Your Warehouse COPY statement to write to the target Capture File should look like this:
COPY CAPTIN TO CAPTOUT 

Tip: copying Capture File records using Warehouse in this fashion will succeed, but will require more disk space the original Capture file. This is because the full Capture File record is written to the target file resulting in wasted disk space at the end of most records.

 

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.

 

Will a Warehouse client run within the MPE/iX POSIX shell?

Yes, it will. Remember that the client needs to be invoked using the MPE/iX POSIX shell naming convention:

shell/iX> /TAURUS/WHII/WH 

 

Will Warehouse client run on Windows 95 or Windows 98?

Taurus Software doesn’t sell Warehouse for Windows 98 or Windows 95, so the Warehouse distribution won’t run on either of these operating systems

 

Will Warehouse run with MPE/iX BIGPIN enabled? 

Yes, it will.

 

How can I test for completion of a script running on MPE/iX?

In the following example, the WH Server on the remote system nt08 was not running when the OPEN was issued from a WH MPE/iX client running a script named chkico. The subsequent SHOWJCW lists the relavent JCW states:

PUB.QA [9]:RUN CHKICO
Warehouse 2.07.3530-M (c) Taurus Software, Inc. 2003
Installed for: Taurus Software mpe02.taurus.com
1> open db remote nt08 user=qa pass=homer oracle scott/tiger
Finding nt08… Trying 57.117.216.20
The Warehouse server is not running on remote computer nt08. (WHERR 13015)
Error(s) in script. No script processing performed. (WHERR 8006)

run wh.whii.inhouse;info=’CHKICO’
Program terminated in an error state. (CIERR 976)

PUB.QA [10]:SHOWJCW
CIERROR = 976
JCW = FATAL0
CJCW = 1
PUB.QA [11]:

This rudimentary snippet is an example of the job completion testing that can be used in a job streams:

whii chkico
if cierror <> 0 or cjcw <> 0
tellop Job chkico FAILED.
tell mgr.qa;*********** chkico FAILED ****************
else
tell mgr.qa;************ chkico complete *************
endif

 

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.