pajama

• You can do onstat -u to get session ids then onstat -g ses <sessionid> to
  get threads. Finally do onstat -g ath to list threads and and look at the ones which have a status of running.
• You can see active threads with 'onstat -g act -r 2'. This will show you active
  threads every 2 seconds. If you will see same thread repeatedly, then you can
  use value of 'rstcb' column of previous output to catch session:
  
  onstat -u | grep value_of_rstcb
  
  Then you can get sql causing high CPU utilization with onstat -g ses session_id"
  

INTRODUCTION

This guide outlines the supported method of adding logical logs in a HDR pair in the IDS environment, where a new log is initially added to the primary and then replicated to the secondary server.

STEPS 

1. This step adds log(s) in a HDR pair environment. Use onparams" command to add the desired number of logs on the primary server, for example: 
   
   $ onparams -a -d logspace 
   
   
2. The onparams operation is automatically replicated on the secondary database server. 
   
   
3. The action of adding logs can be confirmed by checking the online.log messages and a logical log listing (by means of onstat -l" ). 
   The command onstat -l" will display the newly added log(s) with the flag 'A'. 
   The logs are immediately available for use. 
   
   
4. A level-0 archive of the rootdbs and the dbspace containing the newly added log(s) is recommended.

INTRODUCTION

You can change the data replication server type of an IBM® Informix® Dynamic Server (IDS) instance in an HDR pair from Primary to Secondary and Secondary to Primary. 


BEFORE YOU BEGIN

The steps below will only work when data replication has been started with DRAUTO set to 0 (manual), which is the default, in the $ONCONFIG file.

You will need to open two session windows, the first session to connect to the Primary server, and the second session to connect to the Secondary server. After you log in as user informix, set up your environment to administer the database server, you must also check to ensure the Primary server and Secondary server are up and operational.


STEPS

The following steps will show you how to manually change the current Primary server to a new Secondary server, and the current Secondary to a new Primary.

Check server status and message log after each command to ensure the server is in the correct state before the next step. Failing to do so can result in data replication pair out of synchronization and shall require re-establishing the pair from archive.


On the current Primary server

1. Run this command to check the database server status:

onstat -

The output must contain this:

-- on-Line (Prim) -- Up

Note: If it does not show the above then you must correct it before proceeding to the next steps.

2. Shut down the current Primary server, run the following command:

onmode -ky

onmode -d standard

DR: new type = standard
On-Line Mode

onmode -d primary <secondary dbserver name>*

   *<secondary dbserver name> is the former primary database server name.

DR: new type = primary, secondary server name = <secondary dbserver name>
DR: Trying to connect to secondary server = <secondary dbserver name>
DR: Cannot connect to secondary server
DR: Turned off on primary server

onstat -

 -- on-Line (Prim) -- Up

oninit -PHY

Recovery Mode

-- Fast Recovery -- Up

onmode -d secondary <primary dbserver name>*

  *<primary dbserver name>is the former Secondary database server name.

DR: new type = secondary, primary server name = <primary dbserver name>
DR: Trying to connect to primary server = <primary dbserver name>
DR: Cannot connect to primary server
DR: Turned off on secondary server

On-Line Mode
DR: Sending log <log number> (current), size <value> pages, <value> percent used
DR: Sending Logical Logs Completed
DR: Primary server operational

For a while, DBaccess was provided only on the server side, since it is designed as an interactive interface with Informix Dynamic Server. Beginning with CSDK 3.70.xC3, DBaccess became available on the client side. The DBaccess for client-side platforms is available only at the same bit level as the Server side product. For example, when the available Informix Dynamic Server version is 64 bit only, there is no equivalent 32 bit DBaccess client-side product in the CSDK product.

There is no CSDK with DBaccess on the following platforms:

The Smart Large Object file has a name in one of these formats:

For a BLOB value: blob#####.###
For a CLOB value: clob#####.###

예) 파일내용. 오프셋은 16진수로 표현한다

|100,200,/usr/apps/clob9ce7.318|

######.###
The digits of the unique hexadecimal smart-large-object identifier. Automatically assigned to the file when it is created.

start_offset,length,location_path

start_offset
The starting offset (bytes, in hexadecimal) of the smart large object value within the blob/clob file.

length 
The length (bytes, in hexadecimal) of the BLOB or CLOB value.

location_path
The full pathname to the location where the blob/clob file is stored. If a full path is not specified the current directory will be used.

Note: No blank spaces can appear between these values. 

The onconfig parameters TAPESIZE and LTAPESIZE are used in connection with TAPEDEV and LTAPEDEV to determine the size for each tape or file to be used for an ontape backup. At times, the backup size requirement may exceed the size configured for an ontape backup. This is usually not a problem if you are standing by, ready to handle the situation. If you want the backup to fit into a single tape or file, you have to make sure your size estimate is reasonable.

The size needed for an Informix Server Backup using ontape is based on several internal factors. The most important considerations are: 

• when the last backup was done
• the page size used for each dbspace
• how much activity has been transacted since the last backup
• whether it is a level 0, 1, or 2 backup.

To determine backup size requirements, generate an 'onstat -d' listing, and examine the chunk column references for "Size" and "Free". Work with one group of page size at a time to determine a value (K):

1. Review the dbspaces with similar page size (2k, 4k, 8k, etc). Each collection of dbspaces with the same page size is a group (group-P). Add all the totals for (group-P) 'Size' and place it in 'N'.
2. Add all the totals for (group-P) 'Free' and place it in 'F'.
   • Ignore 'size' and 'free' for temp dbspace and sbspace chunks.
3. Subtract F from N (N-F).
   • This will provide the count of pages which are in use (M).
4. Multiply M times the page size (for group-P) to get the number of page units (K ) needed for storage:
   • M * P = K (kb).
5. Repeat steps 1-4 for other page sizes, and add all of the K values. The final result provides a maximum size estimate for the next backup

This can be established with onstat -g ntu or onstat -g ntt output, by reviewing the q-limitsvalue which is displayed in the header section of the results. This value represents the size of a pre-allocated array used to store information about client connections.

The value is calculated as 100 + (poll_threads * 0.7 * num_connections).

Informix DBAs and System Administrators usually find HPL a little bit tricky, due to its own shared memory management engine and specific command line instructions to handle the load and unload jobs control.

This document gathers a compilation of useful links and ease-of-use instructions to quickly set HPL ready to run.

Eventually, some may find the GUI option the best path, but this document intends to prepare the reader to set HPL in just a few steps, thru the command line interface.

1. First of all, you should choose a table to create the HPL job (and the onpload database):

  $ onpladm create job j_items -d "items.unl" -D stores -t items 

   Successfully created Job j_items

where:
• j_items -> job name for the table
• items.unl -> flat file to receive the unloaded table rows
• stores -> database name
• items -> table name

As soon as the first job is created, the onpload database is created too, if is doens't exist already.
If it does exist, you should rename it so it won't cause any trouble.

 
where:
• $PWD -> environment variable created to store the current directory value
• j_items -> job name for the table
• items.log -> error log file name

• This query, generates the CREATE JOB command thru the onpladm facility for all tables in the database, in Express Mode:

select tabname from systables where tabid > 99 and tabtype = "T" into temp a;
output to hpl_create_job.sh without headings
select onpladm create job job_"||trim(tabname)||" -d '/tmp/"||trim(tabname)||".unl' -D <dbname> -t "||trim(tabname) from a    

• This query, generates the CREATE JOB command thru the onpladm facility for all tables in the database, in Deluxe Mode:

select tabname from systables where tabid > 99 and tabtype = "T" into temp a;
output to hpl_create_job_delux.sh without headings
select onpladm create job job_"||trim(tabname)||" -d '/tmp/"||trim(tabname)||".unl' -D <dbname> -t "||trim(tabname)||" -fc" from a    

• This query, generates the job load command thru the onpload facility for all tables in the database:

select tabname from systables where tabid > 99 and tabtype = "T" into temp a;
output to hpl_load_job.sh without headings
select onpload -j job_"||trim(tabname)||" -fl ","-l /tmp/"||trim(tabname)||".log -i 100000 -R /tmp/"||trim(tabname) from a

• This query, generates the job unload command thru the onpload facility for all tables in the database:
  
  select tabname from systables where tabid > 99 and tabtype = "T" into temp a;
  output to hpl_unload_job.sh without headings
  select onpload -j job_"||trim(tabname)||" -fu ","-l /tmp/"||trim(tabname)||".log -i 100000 -R /tmp/"||trim(tabname) from a                                
  

a. Use this tool to remove line feeds, name it join.sh:

:join 
/\\$/{N 
s/\\\n/ / 
b join 
}

b. Use this tool to remove blank lines, name it del_blank.sh:

sed '/^$/d' $1
Here's an example on its use:

cat hpl_load_job.sh | sed -f join.sh > zz 
sh del_blank.sh zz > zzz 
mv zzz hpl_create_job.sh

• PLOAD_SHMAT:
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.hpl.doc/hpl011066502.htm
  When you set the PLOAD_SHMAT environment variable, the pload converter 
  calculates the address using a global attached segment list that is 
  maintained across pload virtual processors. The pload converter attaches
  at the next available address after the highest address on the list, 
  ensuring that the converter always attaches to an unused shared memory 
  segment.
  
  Error messages like this can be seen in the online.log file when PLOAD_SHMAT is not set:
  15:29:46 PLOAD aborting: cpu VP 15 could not attach shared memory at the required address. Set the environment variable PLOAD_SHMAT to 1 and try again. 
  
• IFX_XFER_SHMBASE
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_255.htm
  After the database server allocates shared memory, the database server 
  might allocate multiple contiguous OS shared memory segments. The client
  utility that connects to shared memory must attach all those OS segments
  contiguously also. The utility might have some other shared objects (for
  example, the xbsa library in onbar) loaded at the address where the 
  server has shared memory segment attached. To workaround this situation,
  you can specify a different base address in the environment variable 
  IFX_XFER_SHMBASE for the utility to attach the shared memory segments.
  
  The following error message can be seen in the online.log file:
  15:29:46 shmat: [22]: operating system error 
  15:29:46 Client could not attach server shared memory segment, use IFX_XFER_SHMBASE.

• PLOAD_SHMBASE
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_297.htm 
  Lets you specify the shared-memory address at which the High-Performance Loader (HPL) onpload processes will attach.

• DBONPLOAD
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_220.htm
  Lets you specify the name of the database that the onpload utility of the High-Performance Loader (HPL) will use. If DBONPLOAD is set, onpload uses the specified name as the name of the database; otherwise, the default name of the database is onpload.

• PLCONFIG
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_295.htm
  Lets you specify the name of the configuration file that the High-Performance Loader (HPL) should use. This file must be located in the $INFORMIXDIR/etc directory. If the PLCONFIG environment variable is not set, then $INFORMIXDIR/etc/plconfig.std is the default configuration file.
  
• PLOAD_LO_PATH
  http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids_sqr_296.htm
  Lets you specify the pathname for smart-large-object handles (which identify the location of smart large objects such as BLOB and CLOB data types). If not set, the /tmp pathname will be used.