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.

HP Nonstop TMF - In Detail

The HP NonStop TMF transaction manager is the foundation for building fault-tolerant and disaster-tolerant applications on HP NonStop servers.

For applications that are TMF-enabled, TMF guarantees the consistency and the correctness of the Enscribe and NonStop SQL databases maintained by the applications.

It accomplishes this ability by ensuring that either all of the operations included in the application’s transactions are applied to the database, or none are. Transactions are durable, surviving any fault in a NonStop server, by safe-storing all changes to the database in a mirrored, diskbased audit trail. The TMF audit trail is the heart of TMF.

An application is not informed of the completion of a transaction until its changes have been safe-stored in the audit trail. After a system recovers from a failure, the audit trail’s contents are used to roll back any incomplete transactions. The audit trail can be used to restore files that have been corrupted or that have been accidentally deleted. TMF allows databases to be reorganized online.

Backups can be written to physical or virtual tape without application downtime. Archived audit trails can be accessed to restore corrupted or deleted files while the system is fully operational.

Because transaction durability is guaranteed by the audit trail, the actual data in the databases does not have to be written to disk immediately. Instead, updated data blocks can be batched and written to disk at the convenience of the operating system. Thus, application processing capacity and response times are significantly enhanced by TMF. TMF provides an interface which allows it to support the management of transactions distributed across a heterogeneous network of other systems.

As will be described in upcoming sections, the TMF audit trail plays a key role in many of the HP NonStop data replication facilities. It provides a log of all database changes to be replicated to other systems.

HP NonStop Remote Database Facility (RDF)

HP NonStop Remote Database Facility (RDF)

HP NonStop RDF extends the NonStop server’s fault tolerance to disaster recovery.

Using the audit trail generated by NonStop TMF and operating-system level services, RDF instantly replicates audited database changes to identical target databases on one or more target systems.

Thus, in the event of a failure of a primary system, the application can be recovered to a backup system within minutes and with minimal data loss. RDF will back out any incomplete transactions on the target system when the target system takes over processing.

RDF can be configured to protect individual audited files or tables, the contents of one or more subvolumes, or entire disk volumes. It can replicate changes to one or more target systems.

Alternatively, it can replicate changes from many source databases to a single target database to provide a single backup for multiple production systems. RDF is useful for upgrading hardware and software with no application downtime. The backup system is taken offline and upgraded. Application processing is then redirected to the upgraded backup system, and the other system is upgraded. Though applications on the target system cannot open databases for write access that are being replicated by RDF, they can read the target databases. On the target system, other applications can be running, replicating their database changes to other target systems. If the backup system has to take over processing, the backup applications open the application databases for write access before continuing application processing. There is a small time latency between when transactions are executed on the source system and when they have been successfully replicated to the target system.

Thus, transactions in the replication pipeline may be lost due to a primary system failure. If this loss is unacceptable, RDF implements a Zero Lost Transaction (ZLT) feature. With ZLT, the audit trail disk mirrors are split; and one mirror is located up to 100 kilometers from the production system. No transaction completes until it has been successfully written to the remote mirror. The remote mirror is available to a backup system if the backup system needs to take over processing with no lost transactions.

Nonstop Business Continuity Product Suite : An Introduction

Product Suite Consists of

1. HP NONSTOP TMF
2. HP NONSTOP Auto TMF
3. HP Nonstop RDF
4. HP Shadowbase for Nonstop and other servers
5. HP Nonstop SQL Data Definition Language Replicator(SDR)
6. HP Nonstop AutoSYNC

HP NonStop AutoTMF

HP NonStop AutoTMF

Many legacy applications have been written without using the HP NonStop TMF transaction manager.
In such instances, HP NonStop AutoTMF supports existing application unaudited Enscribe files, allowing applications to be TMF-protected without the need for coding changes or recompiling.

AutoTMF permits such applications to take advantage of the TMF transaction protection of audited databases and to benefit from the performance improvements delivered by TMF.

AutoTMF can be configured to audit only one or any subset of applications or files. Once a file is placed under AutoTMF protection, AutoTMF will dynamically determine effective and efficient transaction boundaries within the application.

These delineated transactions are then written to the database under the TMF transaction manager. By instrumenting an application with AutoTMF, changes to unaudited files are written to a TMF audit trail.

This is a requirement for replicating the data in unaudited files to other databases.

Few Nonstop Fundamentals - V

CHECKPOINT

A checkpoint is a Nonstop specific Term relevant only to Nonstop Process Pairs. The purpose of a checkpoint is to ensure that the two process have identical process state so that if there is a single point of failure, the backup process can take over the completion of processing  without the requesting process needing to do any exception handling.

The purpose of checkpoints are to make primary process failures transparent.

Checkpoints occur on critical region boundaries to ensure all-or-nothing processing process semantics.

The primary process sends checkpoints on critical region boundaries to the backup and waits for acknowledgement before proceeding to the next critical region step.

Its upto Application Architects to determine what the critical regions of a process are.

Few Nonstop Fundamentals - IV

Massively Parallel Processing(MPP) VS 

Symmetical Multi-Processor(SMP)

MPP is a System Architecture that presumes each processor has its own RAM and that other processors can't access it. 

Each processor is presumed to operate as an autonomous system that has mechanism for coordinating work with other processors - typically message bus.

SMP is a system Architecture that presumes two or more processors access the same shared RAM.

To do so, Synchronization mechanisms, (typically semaphones) are used to coordinate access between processes running in competing CPUs.

Few Nonstop Fundamentals - III

IPM

IPM means Inter Process Message, is a type of message that is sent across a message bus to tie the processors of an MPP system together.

In the context of Nonstop, this is a message that is sent between any two processes within a given Nonstop Node or across an EXPAND network on Nonstop Nodes.

These messages are unsolicited requests that are sent to the $RECEIVE message queue of a specified process by another Nonstop Process.

On Nonstop X, InfiniBand is the bus fabric.

RDMA

RDMA means Remote Direct Memory Access, is a capability specific to ServerNet and Infiniband architectures where by the memory of a given CPU can be directly referenced by a process in another CPU without the CPU that owns the memory being involved in the I/O operation.

There is no context Switching, Interrupt or Trap Handling needed to service the I/O.

Everything happens in user mode during the process execution time slice for maximum efficiency.

Few Nonstop Fundamentals - II

Multi Core Processors

Multi Core processors are a more recent evolution of CPU architectures where by sophisticated chip logic and compilers allow processing to be broken into threads of critical regions and submitted to multiple cores simultaneously.

Instead of processor needing to run at ever faster clock speeds, cross-section computing power can be aggregated across multiple processors to achieve the same effect.

TMF

TMF Means Transaction Monitor Facility.
Transaction monitor is a mechanism that  can ensure ACID properties  of a given database update or transactional all-or-none execution consistency respectively. In Nonstop Systems, this transaction monitor is achieve through TMF subsystem.

Its fully integrated with IPM and file system.

Few Nonstop Fundamentals - I

Nonstop Process Pair

A Nonstop Process Pair refers to
       - a primary process residing in a particular CPU
       - and a designated backup CPU residing in another CPU.

They share the same process name and but have a difference Process ID(CPU:PIN).

They can be configured to be amneisa backup processes(i.e they know nothing about the state of other process) or

they communicate with each Other to preserve state using checkpoints.

TACL Security Parameters

The build in TACL Variables are follows.
#PMSEARCHLIST

- A SearchList is list of subvolumes that the TACL software uses to find a program file when the program is invoked using a file name that is not fully qualified.

#TACLSECURITY
It returns a pair of chars , that represent a TACL security.

The first character represents the criterion that determines whether ot not to allow a process to open the TACL process's $RECEIVE for writing.

The second character determines whether to allow an opener with a qualifying name to transfer data to or from #SERVER.

Process Structure in Tandem

When a Process is created, it occupies space in Virtual Memory.
OS divided memory into six sections for Application/system processes.

1. System code space
2. System Libary space
3. System Data Space

4.Application code space
5.Application Data space
6. Application Library space

Code Space - It contains program instuctions and constants from the OBJ file.
Data Space - It is used by a process to store and manipulate Data.
Library Space - Its used by a process to store any routines loaded from libraries

Things to remember in Object Oriented programming

Important things to remember:

Good programs are
Flexible
Robust
Extensible
Easy to understand
Easy to maintain

An object oriented program is a collection of interacting objects.

Good OO programs are characterized by
Encapsulation
information hiding
modular design

Reliance on public method contracts not internal implementation
Loose coupling between objects.
High levels of abstraction -- polymorphic behavior

A program starts by creating one object whose job it is to create all objects that are initially needed.

Objects can create other objects.
Objects can be disposed and cease to exist.
Objects do not need names to function.
Objects should only know the minimum of what they need to know and should do only the minimum of what they need to do.

This is called "loose coupling". Don't make "god objects".

Objects should expose the minimum information and methods needed by the rest of the program. This is another aspect of loose coupling.

When using an object, do not concern yourself with how its methods are implemented, only on what that class defines as the function of that method. Thus a method involves more than just code, but also a "contract" with the rest of the program.

Gathering System Information

To Display the information such as System Name, Number and Operating System Release
numbers and the current SYSnnn, use the SYSINFO Command.

On Nonstop K series system, sysinfo command may not work properly.

We can use RLSEID file which contains system numer and release version.

ex1 :-

FUP COPY $system.sys01.rlseid

FUP COPY $system.sys04.rlseid

How to showing the equivalent Tandem (NSK) process id of the OSS ?process?

The -l option of the ps command shows the CPU,PIN in the ADDR (address) column:

jojo@\rat:~ $ ps -l
F S UID PID PPID C PRI NI ADDR SZ WCHAN TTY TIME
CMD
000 0002 5300 318308420 1 - 175 - 0,347 - - #ptcaamr
00:00 /bin/-sh
000 2002 5300 234422338 318308420 - 175 - 0,491 - - #ptcaamr
00:00 ps


You could also use the '-o addr' option:
jojo@\rat:~ $ ps -o addr,command
ADDR COMMAND
0,347 /bin/-sh
0,410 ps -o addr,command

Binder Timestamp Versus SQL Compile Time

We found this information after much experimentation. The last modified time of a SQL program shows when it was last SQL-compiled - it does not reflect when the source language program was actually compiled. (The opposite is also true: the Binder timestamp does not reflect when the SQL-compile was performed but this information is much less important.) We ran into some problems where the wrong version of a program was used, but it was not possible to tell this from the last modified time information. Using the Binder you can obtain the date and time of both the source language compilation and the SQL-compilation.

To show the source language compilation time, issue the following command:

BIND SHOW SET TIMESTAMP FROM object

To determine the SQL-compilation time, use the following undocumented option of the Binder's INFO INCLUDE command:

BIND FILE object; INFO INCLUDE SQL_OBJ SQL-OBJECT, DETAIL