Striim

Welcome to the Striim Help Center and Community Site

3.10.3 HP NonStop

Follow

Striim can read change data from HP NonStop Enscribe (all versions), SQL/MP (all versions), and SQL/MX (3.2.1 or later).

Setting up HP NonStop with the Striim agent

The Striim agent software for HP NonStop comprises 5 objects that need to be installed on the source HP NonStop system :

  • WAGENT - Agent process responsible for managing the change data capture (CDC) processes

  • SQMXCDCP - CDC process program file for NonStop SQL/MX

  • SQMPCDCP - CDC process program file for NonStop SQL/MP

  • ENSCDCP - CDC process program file for Enscribe

  • WEBACTIO - SQL/MX module used by SQMXCDCP

The 5 files are distributed in PAK files called E<version> (for NonStop) and X<version> (for NonStop X). For example:

  • Striim version 3.9.6: E3096 / X3096

  • 3.9.6.1: E30961 / X30961

  • 3.10.0: E3100 / X3100

  • 3.10.0.1: E31001 / X31001

  • 3.10.1: E3101 / X3101

1. Copy the PAK file to the HP NonStop system using binary FTP file transfer (or equivalent). From a TACL prompt, UNPAK this file info the subvolume in which you wish to install the HP NonStop components using the following command (where $<vol>.<subvol> identify where the object files are to be installed):

UNPAK <PAK file name>,$*.*.*,MYID,LISTALL,VOL $<vol>.<subvol>

2. Once the files are unpaked, the 3 CDC process objects need to changed to be owned by SUPER.SUPER and be LICENSED and PROGID’d. To do this, log on as SUPER.SUPER and execute the following commands:

FUP GIVE (SQMXCDCP,SQMPCDCP,ENSCDCP),SUPER.SUPER
FUP LICENSE (SQMXCDCP,SQMPCDCP,ENSCDCP)
FUP SECURE (SQMXCDCP,SQMPCDCP,ENSCDCP),<security-string>, PROGID

<security-string> is user-determined, however, these files need to be secured so that the userid under which the agent process runs has execute permissions on these object files.

3. If Striim will be used to capture changes from SQL/MP tables, the SQMPCDCP program must be SQL-compiled, as described later in this section under the heading "System log messages about SQL/MP automatic recompilation". If minimizing system log messages about automatic recompilation is not important, any SQL/MP catalog on the system may be used for <your catalog vol-subvol> in those directions. It may be one that already exists, or one you create to use solely for this program.

4. (Optional) Create an Edit file anywhere in the Guardian filespace containing the names of all files and tables you plan to reference from any Striim application. If you do not want to control which files and tables Striim applications can reference, you do not have to create this file. Change data will only be captured and sent for files and tables requested by Striim applications, not necessarily all the files and tables listed in this file.

The file consists of one line per table or file for which change data can be captured. Wildcard patterns are not currently supported. The volume and subvolume names are optional for SQL/MP and Enscribe names, the default volume and subvolume names of the agent process will be added for partially qualified names. SQL/MX names should be fully qualified 3 part names, for example:

  • for SQL/MX: <CATALOG>.<SCHEMA>.<TABLE>

  • for SQL/MP: [<$VOL>.][<SUBVOL>.]<TABLE>

  • for Enscribe: [<$VOL>.][<SUBVOL>.]<FILE>

When SQL/MP tables or Enscribe files on SMF Virtual Disks are to be allowed to be referenced, the logical name of the table or file is what should be specified in this file, not the physical name.

5. If necessary, change the security for WAGENT to match the userid you will use to start the process.

Starting the Striim Agent process on the HP NonStop platform

The agent process (WAGENT) must run at all times to allow the Striim server to make connections and request change data from the HP NonStop platform. The agent can be started from the TACL command prompt as follows:

[PARAM WA-LOGPRIORITY <log-level>]
[PARAM ALLOWED-TABLES <allowed-tables-file-name>]
[PARAM WA-ASSUMED-TIMEZONE <time-zone-code>]
[PARAM WA-CHARSET-FOR-CHAR <charset-name>]
[PARAM WA-CHARSET-FOR-NCHAR <charset-name>]
[PARAM WA-CDC-CPU-LIST "<list-of-CPU-numbers>"]
[PARAM WA-ENCRYPT-ALL {TRUE|FALSE}]
[ADD DEFINE =TCPIP^PROCESS^NAME, CLASS MAP, FILE <TCP/IP-process-name> ]
RUN WAGENT [ / NAME <a-name>, TERM $ZHOME/ ] --agent_port <port-num> --logger_name <ems-collector>

Where:

<log-level> is a number that specifies the highest level of detail for the messages the Striim processes report. Messages at the specified value and below are reported. The permitted values are :

0 FATAL 
1 ALERT 
2 CRIT 
3 ERROR 
4 WARN 
5 NOTICE 
6 INFO 
7 DEBUG 
8 TRACE

The default value is 4, which is WARN, the recommended value for normal use.

<allowed-tables-file-name> is the name of the Edit file you created in step 4 of "Installing the agent." If you do not want to control which files can be accessed by Striim applications, you do not have to specify this parameter.

<time-zone-code> is the code for the time zone used by date values in the files and tables. The valid values (which are not case-sensitive) are:

  • GMT for Greenwich Mean Time

  • LST for Local Standard Time as configured for the NonStop system, ignoring daylight savings time

  • LCT for Local Civil Time as configured for the NonStop system, including daylight savings time (default used if this PARAM is not specified)

<charset-name> is one of the character set names listed in Encoding of character fields and specifies the encoding used for character fields. See that section for a full description of the character set names and the PARAMs with which they are used.

<list-of-CPU-numbers> is a list of CPU numbers in which CDC processes may be started. The point of this is so that when more than one CDC process is running, the CDC processes do not all have to run in the same CPU.  There might be multiple CDC processes due to having multiple TQL applications running or a single TQL application that uses parallel audit trail reading . The CPU numbers may be separated by spaces or commas. The number of a CPU that is down is accepted, but a number that is larger than the highest numbered CPU is not accepted and causes an error and termination of WAGENT. The CPU numbers in the list are used in round robin fashion when choosing the CPU in which the next CDC process is started, skipping any CPU that currently is down. If the WA-CDC-CPU-LIST PARAM is not present, all CDC processes are started in the CPU in which WAGENT is running. The same CPU number may appear in the list more than once. This can be used to use some CPUs. more often than others for CDC processes. See HP NonStop reader properties for a description of how to request parallel audit trail reading.

PARAM WA-ENCRYPT-ALL TRUE encrypts messages between Striim and WAGENT (and all processes started by WAGENT) for applications or flows created WITH ENCRYPTION (see CREATE APPLICATION ... END APPLICATION ). The encryption key is set automatically by Striim. When WA-ENCRYPT-ALL is true, attempting to run an application created without encryption will fail with an error. Similarly, if WA-ENCRYPT-ALL is omitted or FALSE, attempting to run an application created with encryption will fail with an error. To run applications both with and without encryption, start two WAGENT processes, one with WA-ENCRYPT-ALL TRUE and the other without, and specify the port number for the appropriate WAGENT process in the HP NonStop reader properties in the application.

Messages related to this encryption may appear in either the NonStop system or Striim server logs, and do not always specifically mention encryption. When WAGENT expects an encrypted message and decryption fails, or when it expects  an unencrypted message and fails to parse a GPB object, it writes a warning to its log. If Striim sends a start command that WAGENT does not recognize due to mismatched encryption settings, Striim will write an error to its server log and the application will not start.

<TCP/IP-process-name> is the name of the TCP/IP process that the WAGENT and CDC processes should use. The ADD DEFINE command may be omitted if the default process, $ZTC0, is the one that should be used. The ADD DEFINE commands for other DEFINE names that specify TCP/IP settings, such as =TCPIP^HOST^FILE, TCPIP^NETWORK^FILE, etc., also may be included here if the Striim programs must use non-default settings.

<a-name> is a Guardian process name to be used to identify the WAGENT process.

<port-num> is the port number the agent process listens on for connections from the Striim server.

<ems-collector> is the name of an EMS collector process where the agent and CDC processes will write any informational and error messages.

We recommend that you name the agent process to aid process identification, though that is not required.

The Striim processes do not write any messages of their own to the process' home terminal, but if the C++ runtime library reports an error or if one of the processes abends and produces a saveabend file, the messages about those events will be sent to the home terminal. Further, when the agent starts one of the CDC processes, if the agent's home terminal no longer exists, starting the CDC process will fail. For both these reasons, it is best not to let the agent inherit a telnet session's home terminal, but specify a device or process that always exists, such as $ZHOME or a VHS process, as the TERM argument of the RUN command for WAGENT. Using a VHS process would allow you to control where the messages are logged, which probably would make it easier for you to find them, should some serious error occur in a Striim component.

Running the Striim agent as an SCF Persistent Generic Process

To ensure the agent process is always running and available, it is recommended that the process is started and managed as an SCF Persistent Generic Process. (See chapter 3 of the HP NonStop SCF Reference Manual for the Kernel Subsystem for more information on creating and managing generic processes).

Below is an example of the SCF commands that could be used to create and start the agent as a persistent generic process:

ASSUME PROCESS $ZZKRN
ADD #WAGNT, &
NAME $WAGNT, &
HOMETERM $ZHOME, &
CPU FIRSTOF (1,2,3), &
AUTORESTART 10, &
PROGRAM <$vol>.<subvol>.WAGENT , &
DEFAULTVOL <$vol>.<subvol> , &
USERID <group>.<user>, &
STARTMODE APPLICATION, &
STARTUPMSG "--agent_port <port-num> --logger_name <ems-collector>"
[ ADD #WAGNT , &
 ( PARAM ALLOWED-TABLES <$vol>.<subvol>.<file> ) ]
[ ADD #WAGNT , &
 ( DEFINE =TCPIP^PROCESS^NAME, CLASS MAP, FILE <TCP/IP-process-name> ) ]
START #WAGNT

You may use a process name (the NAME argument) other than $WAGNT, but the generic process must be named.

The DEFAULTVOL must be specified to be the volume and subvolume in which the HP NonStop components were installed. If this is not set properly, the Agent will not be able to start the change data capture processes, since it expects their object files to be in the default volume.

If you choose to use the ALLOWED-TABLES PARAM, the filename given in the ADD #WAGNT command for PARAM ALLOWED-TABLES must be the name of the Edit file you created in step 4 of "Installing the agent."

This example shows only specifying the PARAM ALLOWED-TABLES and the DEFINE =TCPIP^PROCESS^NAME, but the other PARAMs and DEFINEs documented in "Starting the Striim Agent process on the HP NonStop platform" also may be specified by including additional ADD #WAGNT commands before the START #WAGNT command.

The home terminal for a Persistent Generic Process defaults to $YMIOP.#CLCI, the system console, if no HOMETERM argument is included. HP recommends using $ZHOME as the HOMETERM for most Persistent Generic Processes. You also could use a VHS process as the HOMETERM if you arrange to configure the VHS process as a Persistent Generic Process that starts before the agent process starts. Using a VHS process would allow you to control where the messages are logged so you could more easily find them if a serious error would occur in a Striim component.

Disabling TMF Audit Compression

The Striim CDC Process for HP NonStop reads and forwards change data from the NonStop TMF audit trail to the Striim server. TMF has the ability to compress audit data records, which means only columns that have changed are audited, or in the case of Enscribe, only the bytes that have changed are audited. To ensure all the field and column data is available to the flows in Striim applications, we recommend that you disable such compression by creating the relevant files and tables with the NO AUDITCOMPRESS attribute.

If TMF audit compression is enabled for a SQL table, change records for UPDATE operations on the table might not contain the values of at least some of the columns that were not changed by the update.  If TMF audit compression is enabled for an Enscribe file, no change records will be created for UPDATE operations on those files.

System log messages about SQL/MP automatic recompilation

When Striim is used to work with change data from SQL/MP tables, embedded SQL code in SQMPCDCP is used to read the description of each of those tables from the SQL/MP catalog in which it is registered. Since similarity checking is not available for SQL/MP catalog tables, each of those embedded queries is automatically recompiled when it is run. There are two automatic recompilations done each time a different catalog is referenced. These automatic recompilations do not affect the performance of capturing the change data from the TMF audit trail and sending it to the Striim server. They occur only at the time the Striim application is started on the Striim server, when it sends the request to start capture of change data from particular tables on the NonStop system.

The SQL/MP compiler reports each automatic recompilation in a message to the system log, and there is no way to turn off or redirect those messages, so at least two automatic recompilation messages will appear each time a Striim application is started. There could be more than two automatic recompilation messages if the Striim application requests change data from tables in more than one catalog. These messages can be ignored safely. They are expected during normal operation and do not indicate anything wrong in the SQL/MP change data capture process. However, if these messages interfere with your regular monitoring of the system log, you can reduce or eliminate them by SQL compiling the SQMPCDCP program to reference the tables in the SQL/MP catalog on your system used most frequently for tables that the Striim applications reference.

If you decide to SQL compile the SQMPCDCP program, run the following commands while logged on as SUPER.SUPER:

VOLUME <Striim vol-subvol>
ADD DEFINE =WEBACT_COLUMNS, CLASS MAP, FILE <your catalog vol-subvol>.COLUMNS
ADD DEFINE =WEBACT_KEYS,    CLASS MAP, FILE <your catalog vol-subvol>.KEYS
ADD DEFINE =WEBACT_PARTNS,  CLASS MAP, FILE <your catalog vol-subvol>.PARTNS
SQLCOMP / IN SQMPCDCP / CATALOG <your catalog vol-subvol>, &
  COMPILE PROGRAM STORE SIMILARITY INFO
FUP LICENSE SQMPCDCP

Where:

<Striim vol-subvol> is the volume and subvolume in which you installed the Striim files

<your catalog vol-subvol> is the volume and subvolume of the SQL/MP catalog most frequently used by the SQL/MP tables referenced by Striim applications. This is not necessarily the volume and subvolume in which the tables themselves reside. Use FUP INFO with the DETAIL option on a SQL/MP table to determine in which catalog it is registered.

The system log messages that report the automatic recompilations do not give the name of the table referenced by the query that caused the recompilation. You will have to determine by other means which SQL/MP tables are being used by Striim applications, then check them to see which SQL/MP catalog is used most frequently. If you register all of your SQL/MP tables in the same catalog, you would not have to do any checking to see which catalog to use in the above commands.

The above method will not eliminate all system log messages about automatic SQL compilations of the SQMPCDCP program unless all the SQL/MP tables used from your Striim applications are registered in the one SQL/MP catalog. However, if it is the case that every HpNonStopSQLMPReader Adapter in every Striim application references tables only from a single SQL/MP catalog, with some additional effort, you could eliminate all the system log messages.

To do this, you would install and run the Striim Agent in multiple subvolumes on the NonStop system and use the above method to SQL compile SQMPCDCP in each of them to use a different SQL catalog on your system. Then, if you are careful to configure each HpNonStopSQLMPReader Adapter with the IP address of the Agent whose copy of SQMPCDCP was SQL compiled with the catalog used by the tables referenced in that HpNonStopSQLMPReader Adapter, this would eliminate all of the system log messages about automatic SQL compilations. This method would require that you have several Agents running rather than just one, but the number of processes running SQMPCDCP would be the same, since each HpNonStopSQLMPReader Adapter instance uses its own process running SQMPCDCP, whether they all use the same Agent or different Agents. It also requires extra effort when configuring Striim applications to use the correct IP address for the tables referenced in the HpNonStopSQLMPReader Adapters. You will have to decide whether eliminating the system log messages about automatic SQL compilation is worth the extra effort.

Even if you install the Agent in multiple subvolumes and SQL compile the multiple copies of SQMPCDCP with different catalog tables, if any of the HpNonStopSQLMPReader Adapters contains a list of tables that are not all in the same SQL/MP catalog, you would still see some system log messages about automatic SQL compilation. This would not cause any malfunction of the Striim applications. It just would not eliminate all of the system log messages that you tried to eliminate.

HP NonStop reader properties

Before you can use the HPNonStopEnscribeReader, HPNonStopSQLMPReader, or HPNonStopSQLMXReader, the Striim change data capture (CDC) processes must be installed on the host as detailed in Setting up HP NonStop with the Striim agent. To read from or write to SQL/MP or SQL/MX using DatabaseReader or DatabaseWriter, the JDBC driver must be installed as described in Installing the HP NonStop JDBC driver for SQL/MX.

property

type

default value

notes

Agent IP Address

String

IP address (or DNS hostname) of the HP NonStop system from which the adapter is to receive change data.

Agent Port No

Integer

TCP port number to be used to communicate with the Agent process. This must match the port number given on the command line when starting the Agent (the program wagent).

Audit Trails

String

merged

List of audit trail name abbreviations from which to read change records and transmit them in parallel TCP sessions to the Striim server. Audit trail name abbreviations are: "MAT', 'AUX01', 'AUX02', ... , 'AUX15'. A value of 'parallel' means to read from all the configured audit trails in parallel. The default value of 'merged' means to merge the change records from all the audit trails into a single stream and send them in a single TCP session. The value is not case-sensitive.

NOTE: If recovery is enabled (see Recovering applications), either leave this blank or specify only a single audit trail.

Block Size

integer

64

Amount of data in KB requested by each read operation when receiving change data from the CDC Process.

Compression

Boolean

False

If set to True, fields with unchanged values are omitted from output. See HP NonStop reader WAEvent fields for details.

Include SYSKEY

Boolean

false

SQL/MP and SQL/MX only: set to true to treat the SYSKEY as if it were a user-defined primary key column. Its value is put into data[0] and before[0], and is NOT put in the ROWID of the metadata. This enables DatabaseWriter to replicate tables that contain no user-defined primary key columns. The target table must contain an extra column (that is not in the source table) to hold the SYSKEY values.

IP Address

String

Leave blank unless instructed otherwise by Striim support.

Name

String

Distinguishes adapter instances in the Agent, and also used as the process name of the Guardian process that is started to collect the change data for this instance of the adapter (the CDC Process). Must be 1 to 3 letters or numbers, beginning with a letter. The Guardian process name is formed by adding "$" to the beginning of this name, and in some cases, one character to the end of this name.

Port No

Integer

TCP port number on which the HP NonStop reader module running in the Striim server listens to get the change data from the HP NonStop system.

Return DateTime As

String

Joda

Set to String to return timestamp values as strings rather than Joda timestamps. The primary purpose of this option is to avoid losing precision when microsecond timestamps are converted to Joda milliseconds. The format of the string is yyyy-mm-dd hh:mm:ss.ffffff.

Start LSN

String

To start reading from a specific position, specify the value of the LSNValue field (including the final semicolon) from a WAEvent (see HP NonStop reader WAEvent fields).

Tables

String

List of file or table names, separated by semicolons, for which change data is requested. All the files or tables must exist at the time the application using this reader is started. See further description of the syntax below.

Trim Guardian Names

Boolean

False

For HPNonStopSQLMPReader and HPNonStopEnscribeReader only:

If set to True, the table names in the Tables property may be specified as <volume>.<subvol>.<name> instead of the usual  \<system>.$<volume>.<subvol>.<name>.

When using the MAP function, the target tables must be specified in the shorter format.

This property has no effect on the forms accepted for the part of an Enscribe name following the colon (:), which specifies the location of the DDL dictionary and record name in that dictionary that describes the layout of the records in the Enscribe file. The dictionary location may not be shortened, even if the Enscribe file name is shortened.

TrimGuardianNames has no effect on the forms accepted for the table or file names used in a file that is specified with the ALLOWED-TABLES param.

The output type is WAEvent. See WAEvent contents for change data for more information.

The format of the Tables property value depends on which type of database the adapter is accessing:

Enscribe

$volume.subvolume.file:$ddl-volume.ddl-subvolume.ddl-recordname

wildcard pattern allowed for $volume.subvolume.file; wildcards are * for any series of characters and ? for a single character

SQL/MP

$volume.subvolume.table

wildcard pattern allowed; wildcards are * for any series of characters and ? for a single character

SQL/MX

catalog.schema.table

wildcard pattern allowed; wildcards are % for any series of characters and _ for a single character; % and _ are always wildcards, there is no way to escape them to represent literal % or _.

Note that when using DatabaseReader or DatabaseWriter, the wildcards for SQL/MP and SQL/MX are % and _. (Enscribe files are not accessible using JDBC, so it is not supported as a DatabaseReader source or DatabaseWriter target.) Wildcards are not supported in ALLOWED-TABLES.

For Enscribe files, the file name is followed by the DDL Dictionary name and record name. The file name is separated from the dictionary name by a colon (":"), and the dictionary name is separated from the record name by a period ("."). $ddl-volume.ddl-subvolume gives the location of an Enscribe DDL dictionary. The ddl-recordname may be a DDL RECORD or a DDL DEF in that dictionary that describes the layout of the records in the Enscribe file. The DDL record can be the same as is used to access the file with Enform or to create a record structure declaration for use when accessing the file in a programming language. The dictionary may be created by either DDL or DDL2.

If the volume and/or subvolume part of a SQL/MP table name or Enscribe file name is omitted, the Guardian default volume and subvolume of the wagent process are used to fill in the missing parts, though it probably would be best not to rely on knowing the default volume and subvolume of the wagent process. The ddl-volume may be be omitted and the default taken from the wagent process, but, again, that probably is best avoided.

For SQL/MP tables or Enscribe files on SMF Virtual Disks, the logical name of the table or file is what should be specified, not the physical name.

These adapters use data from change data capture logs, so you will need to use the META() and IS_PRESENT() TQL functions in your queries. See WAEvent contents for change data for more information.

The default value for AuditTrails should be used unless measurements show that there is a performance bottleneck in either reading the audit trails or in the TCP session from the HP NonStop system to the Striim server. Even then, the default value should be used unless the HP NonStop system actually has multiple audit trails configured, and the disks on which the tables or files listed in the Tables property reside send their change data to more than one of the audit trails.

If parallel audit trail reading is specified with the AuditTrails property, the transmission of change records in the parallel TCP sessions is not synchronized, so a TQL application might receive changes for a given transaction after it receives the COMMIT or ROLLBACK record for that transaction. If the MAT is not included among the audit trail name abbreviations (MAT is included implicitly for 'merged' and 'parallel'), the TQL application will not receive any COMMIT or ROLLBACK records. The writer of the TQL application must keep those facts in mind when designing the application.

For all three NonStop databases, numeric data with a nonzero scale is sent to Striim as a string consisting of the decimal digits with an explicit decimal point that expresses the value of the numeric item. If the numeric item is signed, negative values will also have a minus sign as the first character.

For all three NonStop databases, data represented in TQL as DateTime is converted to GMT from the time zone in which it is stored in the database. Since the database does not include an indication of the time zone with the data, Striim assumes the data is stored in the local civil time of the time zone configured as the TIME_ZONE_OFFSET for the NonStop system. This assumption can be changed by using the WA-ASSUMED-TIMEZONE PARAM when starting the Striim Agent. You can make it use local standard time or GMT. This is described in Setting up HP NonStop with the Striim agent.

For all three NonStop databases, the result of processing any of the unsupported data types is unpredictable.

For all three NonStop databases, the contents of fields of single-byte characters, such as PIC X(n), CHAR(n), etc., are assumed to be encoded as UTF-8 unless the PARAM name WA-CHARSET-FOR-CHAR was specified when WAGENT was started to specify the encoding used for single-byte character fields. See Encoding of character fields for more details.

The contents of fields of double-byte characters, such as PIC N(n), NCHAR(n), etc., are interpreted according to the character set specified by the fields' declarations or the defaults configured for the database. If the PARAM named WA-CHARSET-FOR-NCHAR was specified when WAGENT was started, the encoding given by that PARAM is used for double-byte character fields, overriding any explicit or default specification in the declarations. See Encoding of character fields more details.

In all cases, when character fields are referenced in TQL, they are normal Java strings, encoded in Java's default character set.

Examples:

CREATE SOURCE SQLMPSource using HPNonStopEnscribeReader (
  AgentPortNo:4012,
  AgentIpAddress:'192.0.2.150',
  portno:4013,
  ipaddress:'192.0.2.151',
  Name:'ens',
  Tables:'test.es1:test.es1;test.es3:test.es3'
) OUTPUT TO CDCStream;
  
CREATE SOURCE SQLMPSource using HPNonStopSQLMPReader (
  AgentPortNo:4012,
  AgentIpAddress:'192.0.2.150',
  portno:4013,
  ipaddress:'192.0.2.151',
  Name:'lod',
  Tables:'$data06.test.esa;$data06.test.esb;$data06.test.esc'
) OUTPUT TO CDCStream;

CREATE SOURCE SQLMXSource using HPNonStopSQLMXReader (
  AgentPortNo:4012,
  AgentIpAddress:'192.0.2.150',
  portno:4013,
  ipaddress:'192.0.2.151',
  Name:'lod',
  Tables:'testcat.testsch.sqltest1'
) OUTPUT TO CDCStream;
Encoding of character fields

The encoding that is used for character fields in the database may be specified by using the PARAMs named WA-CHARSET-FOR-CHAR and WA-CHARSET-FOR-NCHAR. The value of WA-CHARSET-FOR-CHAR sets the encoding used to interpret values of single-byte character fields. The value of WA-CHARSET-FOR-NCHAR sets the encoding used to interpret values of double-byte character fields. These PARAMs are set before starting WAGENT. They are optional and may be be given separately or together. If these PARAMs are set, they control the interpretation of all character fields of all tables or files referenced by TQL applications that specify the WAGENT that received the PARAMs.

The values specified for these PARAMs are not validated at the time WAGENT starts. If a value is not one of the ones given in the table below, this is detected at the time a TQL application is started that references a table or file that includes character fields of the type the PARAM controls.

Any of the encoding names may be given as the value of either of the PARAMs. There is no attempt to restrict use of certain encodings to single-byte characters or double-byte characters.

The values that are recognized for these PARAMs are given in the following table. The values are case-sensitive, so enter them exactly as they appear in this table.

eucJP

extended unix code for Japanese

eucKR

extended unix code for Korean

eucTW

extended unix code for Taiwan

ISO8859-1

Latin-1, Western European

ISO8859-2

Latin-2, Central European

ISO8859-3

Latin-3, South European

ISO8859-4

Latin-4, North European

ISO8859-5

Latin/Cyrillic

ISO8859-6

Latin/Arabic

ISO8859-7

Latin/Greek

ISO8859-8

Latin/Hebrew

ISO8859-9

Latin-5, Turkish

SJIS

Shift JIS, a common encoding of Japanese Kanji characters

UCS-2

The original 2-byte, big-endian Unicode encoding

UTF-16

The current standard 2-byte, big-endian Unicode encoding

UTF-8

The most common Unicode encoding

HP NonStop reader WAEvent fields

The output data type for HPNonStopEnscribeReader, HPNonStopSQLMPReader, and HPNonStopSQLMXReader is WAEvent. The fields are:

metadata: To retrieve the values for these fields, use the META function.

  • OperationName: ROLLBACK, COMMIT, INSERT, DELETE, or UPDATE

  • TimeStamp: date and time of the operation (Joda DateTime, milliseconds since 1970-1-1 GMT)

  • TxnID: transaction ID

  • TableName (returned only for INSERT, DELETE, and UPDATE operations): fully qualified name of the table

  • AuditTrailName: logical name of the TMF audit trail from which the record came (either 'MAT' or 'AUX01' through  'AUX15') or, if the audit trails are not being read separately in parallel, 'MERGE'.

  • LSNValue: The TMF audit trail position of the event. This may be used to start from a specific position by specifying it as the StartLSN value in the reader (see HP NonStop reader properties).

  • PK_UPDATE: Always false.

  • Rollback: 1 if the record was generated as a result of the rollback of the associated transaction, otherwise 0

  • ROWID (returned only for INSERT, DELETE, and UPDATE operations): the record address, record number, or SYSKEY, or null if the table or file has none of those items. This property is meaningful only for tables or files with a system-generated key, such as entry-sequenced files/tables, relative files/tables, and key-sequenced tables with no user-defined primary key. It is present but null for key-sequenced files and key-sequenced tables that have a user-defined primary key. If the IncludeSYSKEY property is true, the SYSKEY gets put into data[0] (and before[0]) and is not put into ROWID.

  • TxnSystemName: the name of the NonStop system on which the current transaction started. (Useful when converting TxnID to HP's normal human-readable format for transaction IDs.)

To retrieve the values for these fields, use the META function. See Parsing the fields of WAEvent for CDC readers. For TxnSystemName, you may use the NSK_TXN_STRING function to convert its value to a human-readable format. For example:

CREATE CQ tst54cq
INSERT INTO tst54cqstream
SELECT meta(s,"OperationName").toString(),
  meta(s,"TxnID").toString(),
  meta(s,"TxnSystemName").toString(),
  NSK_TXN_STRING(meta(s,"TxnID").toString(),meta(s,"TxnSystemName").toString()),
  CASE WHEN IS_PRESENT(s,data,0) = true
    THEN TO_STRING(data0)
    ELSE 'nothing' END,
  CASE WHEN IS_PRESENTis_present(s,data,1) = true
    THEN TO_STRING(data1)
    ELSE 'nothing' END
FROM tst54stream s;

data: an array of fields, numbered from 0, containing:

  • for an INSERT operation, the values that were inserted.

  • for an UPDATE, the values after the operation was completed; if the HP NonStop reader's compression property is True (see HP NonStop reader properties), only the modified values.

    If TMF audit compression is specified for a table and the HP NonStop reader's Compression property is false, then the values of columns that were changed will be included and the values of some of the columns that were not changed might also be included (whichever makes the TMF audit trail record shorter). If TMF audit compression is specified for an Enscribe file, no change records are created for UPDATE operations.

  • for a DELETE operation, the values that were deleted; if the HP NonStop reader's compression property is True (see HP NonStop reader properties), contains only the value of the primary key column (unless there are no user-defined key columns, in which case all column values are included)

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

For Enscribe files, the entries in the data[] array that correspond to DDL fields that are beyond the end of the current record are omitted. The IS_PRESENT() function can be used to determine whether a field's value is included or omitted if it is not possible to determine that from the value of a field that is always present that gives the record type of the current record.

The value of fields of character type that start before the end of the current record, but whose declared length extends beyond the end of the current record, are included in the data[] array, but only the characters up to the end of the current record are used as the value of that field. IS_PRESENT() returns true for such fields. The value of fields of any other data type that start before the end of the current record, but whose declared length makes them extend beyond the end of the current record, are omitted from the data[] array, and IS_PRESENT() returns false for them.

before (for UPDATE operations only): the same format as data, but containing the values as they were prior to the UPDATE operation.

  • If the HP NonStop reader's compression property is True (see HP NonStop reader properties), before contains only the value of the primary key columns.

  • If the HP NonStop reader's compression property is False, and TMF audit compression is enabled for a file, before contains the values of the primary key columns and the values before the update of any column whose value was changed, but generally the values of columns whose values were not changed generally are omitted (though some of them might be included).

  • If a table has no user-defined primary key columns, before contains the values of all columns before the update was done, except if TMF audit compression is enabled for the table, the values of columns that were not changed usually will be omitted.

  • For Enscribe files, the entries in the before[] array that correspond to DDL fields that are beyond the end of the current record are handled the same as was described above for the data[] array. Note that for Enscribe UPDATE operations, a record may have a different length after the operation than it had before.

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

For SQL/MP and SQL/MX tables that contain a SYSKEY column, the SYSKEY column value is not included in data or before. It is put only into the ROWID part of metadata, unless includeSYSKEY is specified to be true, in whcih case the SYSKEY is put into data[0] (and before[0]) and is not put into ROWID.

For SQL/MP and SQL/MX tables that have the TMF auditcompress attribute set, the change records for updates are guaranteed to contain values only for the columns actually changed and for key columns. Other columns will have null in their spots in data and before. Sometimes the values for other columns will be included if it makes logging the change more efficient, but that cannot be relied upon. If you want update operations to show the values of all the columns, be sure the auditcompress attribute is not set for the tables in question. This affects only updates. Inserts and deletes always show the values of all the columns.

Functions for HP NonStop transaction IDs

function

description

notes

NSK_CNVT_TXNID_TO_UNSIGNED(txnid)

returns the unsigned representation of a NonStop system transaction ID

While Striim always represents a NonStop system transaction ID as an unsigned value, other software, such as Golden Gate, can represent NonStop system transaction IDs as signed values. When the NonStop system number is larger than 126, representing it as signed yields a negative value, which will not be the same as the unsigned value of the same transaction ID. This function converts a negative transaction ID's value to the equivalent positive value, and leaves a positive transaction ID's value unchanged.

It is not necessary to use NSK_CNVT_TXNID_TO_UNSIGNED to convert transaction IDs that will be passed to either NSK_TXNS_ARE_SAME or NSK_TXN_STRING, since they already do the conversion to unsigned. NSK_CNVT_TXNID_TO_UNSIGNED is provided in case you have some other reason to make sure a transaction ID is represented as unsigned.

NSK_TXN_STRING(txnid,systemname)

returns HPE's human-readable form for a transaction ID. txnid is the value of the TxnID field of a WAEvent, and systemname is the TxnSystemName field of the same WAEvent.

This function is useful only if you want to display NonStop system transaction IDs in the same format that HPE's software displays them. That could be helpful if you have to investigate the history of the transaction on the NonStop system. See HP NonStop reader WAEvent fields for an example.

NSK_TXNS_ARE_SAME(txnid1,txnid2)

returns true if the two string arguments identify the same transaction, regardless of differences in how the transaction IDs are represented

Can be used for any comparison of transaction IDs from NonStop systems, but really only needed when comparing transaction IDs from NonStop systems that came to the TQL application via different software products. For example, one transaction ID came via an HPNonStopSQLMXReader adapter and the other came from Golden Gate's Extract, and then only if some of the NonStop systems have system numbers greater than 126. Striim Source adapters always interpret transaction IDs from NonStop systems as unsigned 64-bit values, while other software might interpret the transaction IDs as signed 64-bit values, giving different representations of the same transaction ID.

HPNonStopEnscribeReader data type support and correspondence

Enscribe type

TQL type

PIC X(n)

string

PIC N(n)

string

PIC 9(1-4) COMP

int

PIC S9(1-4) COMP

short

PIC 9(5-9) COMP

long

PIC S9(5-9) COMP

int

PIC 9(10-18) COMP

unsupported

PIC S9(10-18) COMP

long

PIC 9(n)V9(s) COMP

string

PIC S9(n)V9(s) COMP

string

PIC 9(1-3) COMP-3

int

PIC 9(4-9) COMP-3

long

PIC 9(10-18) COMP-3

long

PIC S9(1-3) COMP-3

short

PIC S9(4-9) COMP-3

int

PIC S9(10-18) COMP-3

long

PIC 9(n)V9(s) COMP-3

string

PIC S9(n)V9(s) COMP-3

string

PIC 9(n)

long

PIC T

long

PIC T9(n)

long

PIC 9(n)V9(s)

string

PIC T9(n)V9(s)

string

PIC 9(n)T

long

PIC 9(n)V9(s)T

string

PIC S9(n)

long

PIC S9(n)V9(s)

string

PIC 9(n)S

long

PIC 9(n)V9(s)S

string

TYPE CHARACTER n

string

TYPE BINARY 8 UNSIGNED

int

TYPE BINARY 8

short

TYPE BINARY 16,0 UNSIGNED

int

TYPE BINARY 16,0

short

TYPE BINARY 32,0 UNSIGNED

long

TYPE BINARY 32,0

int

TYPE BINARY 64,0 UNSIGNED

unsupported

TYPE BINARY 64,0

long

TYPE BINARY n,s UNSIGNED n = 16, 32 s > 0

string

TYPE BINARY n,s n = 16, 32, 64 s > 0

string

TYPE FLOAT 32

double

TYPE FLOAT 64

double

TYPE COMPLEX

two doubles

TYPE LOGICAL 1

int

TYPE LOGICAL 2

short

TYPE LOGICAL 4

int

TYPE ENUM

short

TYPE BIT n

short

TYPE BIT n UNSIGNED

int

TYPE SQL VARCHAR

string

TYPE SQL DATE

string

TYPE SQL TIME

string

TYPE SQL TIMESTAMP

string

TYPE SQL DATETIME

string

TYPE SQL INTERVAL

string

Any Enscribe field that has the SQLNULLABLE attribute in its DDL description is represented in the database as two fields: A two-byte null indicator field, followed by the actual data field. Striim interprets the null indicator field to determine whether the data field has a valid value or is null, so such fields appear as a single field in the WAEvent. Note that the SQLNULLABLE attribute is different than the NULL attribute that DDL also supports. Striim ignores the NULL attribute.

An Enscribe field of type COMPLEX is represented as two 32-bit floating point values in the database. Striim puts two double values into the WAEvent for each type COMPLEX field – first the real part then the imaginary part. Striim creates field names for those two fields by appending "_R" and "_I" to the name declared for the COMPLEX field in the DDL.

An Enscribe field of type SQL VARCHAR is represented in the database as two fields: A two-byte length field, followed by a character field of the maximum possible length, as given in the SQL VARCHAR declaration. Striim puts just a single string into the WAEvent for the SQL VARCHAR field. This field contains the number of characters from the second field that are indicated by the first field.

Enscribe field types SQL DATE, SQL TIME, SQL TIMESTAMP, SQL DATETIME and SQL INTERVAL are synonyms for fixed-length character fields of the appropriate length for the specific date-time or interval type declared. For example TYPE DATETIME YEAR TO DAY declares a 10-character field whose values are expected to represent a date in the form such as "2015-05-20". Striim does not interpret the values of SQL DATETIME or SQL INTERVAL fields, but simply puts them into the WAEvent as strings. The exact length of each of SQL DATETIME and SQL INTERVAL type is documented in Table D-12 and Table D-13 in HP's "Data Definition Language (DDL) Reference Manual", in the chapter "Dictionary Database Structure", at the end of the description of the DICTOBL file. The types SQL DATE, SQL TIME, and SQL TIMESTAMP are missing from those tables. They are shorthand notation for:

  • SQL DATE = SQL DATETIME YEAR TO DAY

  • SQL TIME = SQL DATETIME HOUR TO SECOND

  • SQL TIMESTAMP = SQL DATETIME YEAR TO FRACTION

HPNonStopSQLMPReader data type support and correspondence

SQL/MP type

TQL type

CHAR(n)

string

CHAR VARYING(n)

string

VARCHAR(n)

string

NATIONAL CHARACTER(n)

string

NCHAR(n)

string

NCHAR VARYING(n)

string

SMALLINT UNSIGNED

int

SMALLINT SIGNED

short

INTEGER UNSIGNED

long

INTEGER SIGNED

int

LARGEINT

long

REAL

double

DOUBLE PRECISION

double

FLOAT(n)

double

DATE

DateTime

TIME

string

TIMESTAMP

DateTime

DATETIME YEAR TO x

DateTime

DATETIME x TO y x ≠ YEAR y = any

string

INTERVAL

string

NUMERIC(1-4,0) UNSIGNED

int

NUMERIC(1-4,0) SIGNED

short

NUMERIC(5-9,0) UNSIGNED

long

NUMERIC(5-9,0) SIGNED

int

NUMERIC(10-18,0) SIGNED

long

NUMERIC(n,s) UNSIGNED n ≤ 9 s > 0

string

NUMERIC(n,s) SIGNED s > 0

string

DECIMAL(n,0) UNSIGNED

long

DECIMAL(n,0) SIGNED

long

DECIMAL(n,s) UNSIGNED s > 0

string

DECIMAL(n,s) SIGNED s > 0

string

PIC X(n)

string

PIC 9(1-4) COMP

int

PIC S9(1-4) COMP

short

PIC 9(5-9) COMP

long

PIC S9(5-9) COMP

int

PIC S9(10-18) COMP

long

PIC 9(n)V9(s) COMP

string

PIC S9(n)V9(s) COMP

string

PIC 9(n)

long

PIC S9(n)

long

PIC 9(n)V9(s)

string

PIC S9(n)V9(s)

string

HPNonStopSQLMXReader data type support and correspondence

SQL/MX type

TQL type

PIC X(n)

string

CHAR(n)

string

CHAR VARYING(n)

string

VARCHAR(n)

string

NATIONAL CHARACTER(n)

string

NCHAR(n)

string

NCHAR VARYING(n)

string

SMALLINT UNSIGNED

int

SMALLINT SIGNED

short

INTEGER UNSIGNED

long

INTEGER SIGNED

int

LARGEINT

long

NUMBER(1-4,0) UNSIGNED

int

NUMBER(1-4,0) SIGNED

short

NUMBER(5-9,0) UNSIGNED

long

NUMBER(5-9,0) SIGNED

int

NUMBER(10-128,0) UNSIGNED

string

NUMBER(10-18,0) SIGNED

long

NUMBER(19-128,0) SIGNED

string

NUMBER(1-9,s) UNSIGNED s > 0

string

NUMBER(10-128,s) UNSIGNED

string

NUMBER(1-18,s) SIGNED s > 0

string

NUMBER(19-128,s) SIGNED

string

NUMERIC(1-4,0) UNSIGNED

int

NUMERIC(1-4,0) SIGNED

short

NUMERIC(5-9,0) UNSIGNED

long

NUMERIC(5-9,0) SIGNED

int

NUMERIC(10-128,0) UNSIGNED

string

NUMERIC(10-18,0) SIGNED

long

NUMERIC(19-128,0) SIGNED

string

NUMERIC(1-9,s) UNSIGNED s > 0

string

NUMERIC(10-128,s) UNSIGNED

string

NUMERIC(1-18,s) SIGNED s > 0

string

NUMERIC(19-128,s) SIGNED

string

DECIMAL(n,0) UNSIGNED

long

DECIMAL(n,0) SIGNED

long

DECIMAL(n,s) UNSIGNED s > 0

string

DECIMAL(n,s) SIGNED s > 0

string

REAL

float

DOUBLE PRECISION

double

FLOAT(n)

double

DATE

DateTime

TIME(n)

string

TIMESTAMP(n)

DateTime

INTERVAL

string

PIC 9(1-4) COMP

int

PIC S9(1-4) COMP

short

PIC 9(5-9) COMP

long

PIC S9(5-9) COMP

int

PIC S9(10-18) COMP

long

PIC 9(n)V9(s) COMP

string

PIC S9(n)V9(s) COMP

string

PIC 9(n)

long

PIC S9(n)

long

PIC 9(n)V9(s)

string

PIC S9(n)V9(s)

string

3.10.3
Was this article helpful?
0 out of 0 found this helpful

Comments