NSSQL : Varchar columns

Varchar in COBOL

Invoke of a varchar in COBOL gives the following result:_

invoke testtab format cobol;

* Record Definition for table

* Definition current at 16:08:08 - 09/06/01

01 TESTTAB.
    02 NAAM PIC X(4).
    02 LANG.
             03 LEN PIC S9(4) COMP.
             03 VAL PIC X(255).__

Assigning a value to a VARCHAR column in COBOL

On insert, the length-field of the VARCHAR-columns must be specified by the program as well
as the value-field.

The length-field will be filled automatically when the TRIM-function is used._

Example:_
INSERT INTO =EMS-LG
(
. . .
, TXT
. . .
)
VALUES
(
. . .
, TRIM(:VAL OF TXT OF WS-EMS-LG)
. . .
) _

Note: when the source-string is nullable, an indicator-field has to be used as well._
Example_
TRIM(TRAILING " " FROM ( :VAL OF TGV-REK-NR OF table_
INDICATOR :INDICATOR OF TGV-REK-NR OF table))

Assigning a value to a VARCHAR column in SQLCI

If you want to assign a value to a varchar-column with a string that is longer than 80
characters using SQLCI, then use the following syntax:_

insert into =test-tabel values ("test",
"1234567890123456789012345678901234567890123456789012345678901234567
890" ||
"1234567890123456789012345678901234567890123456789012345678901234567
890" ||
"1234567890123456789012345678901234567890123456789012345678901234567
890" ||
"123456789012345678901234567890123456789012345");_
--- 1 row(s) inserted._

Select in SQLCI

Selecting a varchar column in SQLCI only shows the first 80 characters._

select long from testtab;_

LONG
-------------------------------------------------------------------------------------------------_
123456789012345678901234567890123456789012345678901234567890123456789012345
67890_
--- 1 row(s) selected._

If you are interested in all characters, use the report writer command SET STYLE. Change the
style-option VARCHAR_WIDTH. Maximum is 255._

set varchar_width 255;

select langejan from testtab;_

LANGEJAN_
--------------------------------------------------------------------------------_
--------------------------------------------------------------------------------_
---------------_
123456789012345678901234567890123456789012345678901234567890123456789012345
67890
123456789012345678901234567890123456789012345678901234567890123456789012345
67890
123456789012345678901234567890123456789012345678901234567890123456789012345
67890
123456789012345_

--- 1 row(s) selected._

For columns even longer than 255 bytes the LIST_COUNT-option of the SQLCI-session can be
used:_

set list_count 1;_

select constrainttext from =constrnt
where tablename like upshift(“%BTCHFILE%”);_
detail constrainttext as C3000.75;_

list first; (or short: l f )

(also list next / list all can be used)_

**** next = enter_
**** to cancel the SELECT when not all result rows are shown
cancel;_
set list_count all;_

NSSQL : Nullable columns

Nullable columns in search-conditions Sometimes the use of nullable columns may lead to unexpected results. Note that a row is not selected when the expression in the search-condition evaluates to NULL. Example: Table definition: -- Definition of table    (      ACT_NR                           PIC 9(2) COMP NO DEFAULT NOT NULL   , KANTOOR                        PIC S9(4) COMP NO DEFAULT NOT NULL   , LOCATIE                           PIC 9(2) COMP NO DEFAULT NOT NULL   , VP                                      PIC S9(4) COMP NO DEFAULT   ) Actual data select * from rekening ACT_NR  KANTOOR  LOCATIE  VP    ------        -------          -------    ------       1              1                  1         11       6              1                  1          ? --- 2 row(s) selected. The following query might not deliver the result you expect select * from rekening where vp <> 55; REK_NR  KANTOOR  LOCATIE  VP   ------           -------        -------    ------      1                 1                1         11 --- 1 row(s) selected. The row with a NULL value in the vp-column is not selected. NULL is not different  from 55 but also not equal to 55. It is simply unknown. If the row with the NULL-value has to be selected also, then following syntax has to be used: select * from rekening where vp <> 55 or vp is NULL; REK_NR  KANTOOR  LOCATIE  VP   ------          -------         -------    ------       1               1                1         11       6               1                1          ? --- 2 row(s) selected. Nullable columns in COBOL Another issue to watch carefully is the use of a nullable column in a search-condition in a COBOL program. If the value of a nullable column has to be compared to a host-variable in a search-condition, and the host-variable must be able to contain NULL, then the following construction has to be used: Select ……………   from ……………………   where <other selection criteria>   and (column_name = :valu of column-name and :indicator of column-name > -1            or column_name is null                          and :indicator of column-name <  0) Do not use the following construction (SQL does not recognize the indicator): Select ……………   from …………………… where <other selection criteria>    and (column_name = :valu      of column-name             indicator :indicator of column-name) Suppose a table TEMP. The invoke under SQLCI gives the following result: invoke testtab; -- Definition of table -- Definition current at 16:19:11 - 09/06/01   (     TEMP-ID                        PIC X(5) NO DEFAULT NOT NULL   , OMS                            PIC X(80) NO DEFAULT   , OMS-LANG                       VARCHAR(255) NO DEFAULT NOT NULL   ) Invoke in a COBOL program results in: invoke temp as temp 01 TEMP.     02 TEMP-ID                          PIC X(5).     02 OMS-I                            PIC S9(4) COMP.     02 OMS                              PIC X(80).     02 OMS-LANG.        03 LEN                           PIC S9(4) COMP.        03 VAL                           PIC X(255). Using this type of invoke in COBOL the nullable column cannot be treated as one object. It results in two host-variables on the same level (OMS-I en OMS). This structure also differs from the host-variable structure for a varchar column. A host-variable for a varchar has two levels: the column-name on the highest level and the “LEN” and  “VAL” fields at the lower level. Rather use the next syntax in COBOL programs: invoke temp as temp null structure; Result: 01 TEMP.     02 TEMP-ID                          PIC X(5).     02 OMS.        03 INDICATOR                     PIC S9(4) COMP.        03 VALU                          PIC X(80).     02 OMS-LANG.        03 LEN                           PIC S9(4) COMP.        03 VAL                           PIC X(255).  Now the host-variable for the nullable column has two levels, with at the lowest level two fields with the names “indicator” en “valu”.

NSSQL : Block buffering

Block buffering

Invalidation of buffers When Sequential Block Buffering (VSBB of RSBB) is used for a cursor, then the block-buffer can become invalid when the same process executes a stand-alone INSERT, UPDATE or DELETE against the same table. This may have a negative impact on performance because the block-buffer has to be rebuilt at the next fetch, which results in a physical I/O. Avoid using stand-alone updates and deletes in combination with cursors on the same table. Code the FOR UPDATE OF option on the cursor and use UPDATE/DELETE WHERE CURRENT OF cursor. Range-locks at insert When Sequential Block Buffering (VSBB of RSBB) is used for sequential inserts, this can have a negative impact on concurrency. In this situation NS-SQL issues a range-lock from the last row inserted until the next existing row in the table. If there are no following rows, then the range-lock is extended to End-Of-File. Because of this range-lock no other process can insert rows in this range. If you want to avoid this use the control statement SEQUENTIAL INSERT OFF

PATHWAY Domains and ACS in ACTIVE-ACTIVE Solution

To Maintain ACTIVE-ACTIVE and main requirements for Disaster Recovery is,

 -  Maintaining a copy of the application database on remote systems.

 -  The application must be resident on the remote systems as well.

 -  Equally important is that the applications and the application environment be identical (or at least similar) on all systems in order to avoid failover faults. This requirement adds to the complexity of managing active/backup configurations and exposes these systems to human error.

HP NonStop TS/MP offers an elegant solution to this conundrum, Pathway domains. In a single NonStop system, Pathway provides an environment (PATHMON) that implements application scalability by distributing workloads across dynamic pools of application server processes (serverclasses).

A serverclass spans multiple processors within the NonStop system. The number of servers in a serverclass can be dynamically scaled up or down to meet existing transaction loads. Pathway’s Application Cluster Services (ACS) distributes transactions to servers in a serverclass to

maintain load balancing.

If a processor fails in a NonStop server, ACS distributes transactions to application servers residing on surviving processors. Thus, recovery from a processor failure in a NonStop system

is virtually instantaneous. PATHMONs are a fundamental mechanism of the fault-tolerant capabilities of NonStop servers.

PATHMONs can be distributed across multiple NonStop servers in an application network. Such a set of distributed PATHMONs is called a pathway domain. In a Pathway domain, serverclasses are distributed not only among processors with in a NONSTOP NODE but also among Nonstop nodes in an Application Network.

Pathway domains furnish 3 important advantages for business continuity.

1) The system administrator, when configuring the domains, can ensure that all processes in a server class across all domains are derived from a common executable. Consequently, The Application Processes are automatically consistent across all NONSTOP NODEs, thus eliminating failover faults.

2) If a NONSTOP System fails, further transactions are routed instantly by ACS to application processes running in surviving nodes. Thus, recovery from a system failure in instantaneous.

3) Since all of the domains in a Pathway Domain are independently configured, upgrades may be made with ZERO downtime by taking down a domain, upgrading it , returning to service and rolling the upgrade through the other domains at the same time.

To avoid a single point of failure, that portion of each serverclass resident in a NonStop server node should have its own local application database.

These databases must be kept synchronized so that the applications in each node have the same view of the application database. It is a function satisfied by the use of Shadowbase active/active replication. Shadowbase ensures that an update to any one database is immediately reflected in all of the application’s distributed databases.

HP NonStop AutoSYNC

From a disaster-recovery viewpoint, the role of RDF and

Shadowbase is to maintain an accurate and current image of

audited databases on a backup system. However, this role is not

sufficient to ensure reliable takeover by a backup system if the

primary system fails. There are numerous other files that define

the application environment, and these files must all be mapped

accurately to the backup system. They include unaudited data files,

configuration files, and program executables.

Typically, when a critical file is changed on the primary system,

manual operations must be executed to move a copy of the file to

the backup system. This procedure is subject to human error and

can lead to configuration drift. If the configuration of the backup

system is not identical to that of the primary system, a failover fault

is likely if the primary system fails. The backup system may refuse

to come up or be restarted with an incorrect version of software.

HP AutoSYNC addresses this challenge.

It automatically ensures that the application environment on the

backup system is identical to that of the primary system.

Using a configured list of files that are important to the
application environment, AutoSYNC

monitors these files on the primary system and immediately

replicates changed files to the backup system. The backup system

configuration is therefore always synchronized with the primary

system, thereby guaranteeing a successful failover. AutoSYNC can

be configured to replicate files to any number of target systems.

Add caption

Virtually all file types are supported, including audited

and unaudited NonStop SQL tables; audited and unaudited

structured files; unstructured files such as edit files, OSS files,
and directories; and partitioned files. Files necessary to start and
maintain applications also are handled by AutoSYNC. Included are
configuration files, batch files, object/source files, OSS files, BLOBs,
TACL macros, and Obey files.

AutoSYNC supports triggers that can initiate any function

whenever a file is changed. Triggered functions can include
executing a TACL command, compiling a SQL program, or starting
a program such as a batch job. AutoSYNC also supports automatic
software distribution and can be used to initially load systems being
brought online by synchronizing the files of the new systems with
those of a primary system.