Common Informix commands

I. Common commands
Oninit changes the system from off-line mode to on-line mode
Oninit-Iy (initialize the database and delete all dbspace and Chunk)
Oninit-s (offline --> static)

Onmode-ky offline
Onmode-s graceful shutdown --> quiescent (online to static, let the user finish processing)
Onmode-u immediate shutdown --> quiescent (online to static, all users terminate immediately)
Onmode-M quiescent ---> online
Onmode-Z Sid deletes a session

Onmode-l moves the currently used logic log down
Onmode-C enforces the checkpoint operation, that is, onmonitor-> force ckpt

Onstat-view Server Status
Onstat-d view dbspace and chunk
Onstat-l view logical logs
Onstat-F view the page refresh and type of data written to the disk. There are three ways to write data from the shared memory buffer to the disk page (FG: frontend LRU: queue write Chunk: block write)
Onstat-r print LRU queue
Onstat-P displays the Basic I/O and performance configuration files of the system.
Onstat-C view onconfig File
Onstat-M view message logs
Onstat-F: view the dbspace affected by dataskip
Onstat-x View transactions
Onstat-K displays all active locks

Onstat-G sub_option Option
Onstat-g ses/SQL
Onstat-r <value> Option repeated every <value> second
Onstat-G Act displays all activity clues in the system
Onstat-G ath-R 2 shows all activity clues in the system every two seconds

Onstat-I Interaction Mode
> SQL
> SQL Sid


IPCS view shared memory
Ipcrm deletes a shared memory
Ipcrm-m id
Ipcrm-s ID


Ontape command:
1) Zero-level (primary or secondary) backup
$> Ontape-S
$> Ontape-S-l 0
$> Ontape-S-l 1
$> Ontape-S-l 2

Automatic logical log file backup
$> Ontape-

3) continuous logical log backup (one log file is full, and one is automatically backed up by the online system. You can insert the disk to the tape drive and enable IDs to automatically back up the data. To occupy the tape drive)
$> Ontape-C

4) Backup Recovery (corresponding to zero-level backup or first-level backup or second-level backup)
$> Ontape-R (for detailed procedures, see ontape restoration document)

5) backup and recovery of several dbspaces (logical log backup must exist; otherwise, it will still be unavailable after physical recovery)
$> Ontape-r-D dbs1 dbs2...

6) physical recovery used for HDR Configuration
$> Ontape-P

7) modify the Database Log Mode
$> Change ontape-S-B db_name to buffer_log, that is, the buffer log mode.
$> Ontape-S-u db_name is changed to unbuffer_log, that is, the non-buffer log mode.
$> Ontape-S-N db_name is modified to a log-free database without logs, that is, transactions are not supported, and any modifications to the database are not recorded in logical logs.
$> Ontape-S-A db_name is changed to the ANSI logging mode. This mode cannot be changed to other modes, so this option is generally not used.


Oncheck command

Oncheck-PE extend
Oncheck-ce
Oncheck-Cr reserved pages
Oncheck-CD tblspace data rows including bitmap
Oncheck-ci table indexes Databases

Operations on logical logs:
Log On As an Informix user,

$ Onmode-uy (switch from online to quiescent, and all users stop immediately)
Onmode-M (switch to online)

$ Onparams-a-d logdbs-s 100000

Logdbs is the dbspaces name, and-s 100000 indicates that the space is increased by MB.

Execute the preceding command four times in a row, so that the new logical log space is 500 mb in total.
Onstat-L to view logical logs, delete the first three old logical logs,
Make a level 0 backup before deletion

# Ontape-S-l 0
Execute this command for level 0 backup. We recommend that you end your work in the business system every day if you allow
Perform a level-0 backup. After the backup is completed, manage the backup tape and mark it.

$ Onparams-D-l logid

Logid is the ID of the logical log. You can use onstat-L to view the ID, and then delete it according to the ID.
Except 3 old logic logs.

Move the currently used logic log down
$ Onmode-l


Iii. dbspace and Database Import and Export

1. Add a new dbspace: (datadbs, 15 m, offset is 0)
Onspaces-c-d datadbs-P/home/Informix/datadbs-o 0-s 15000

2. Add a chunk in the dbspace datadbs: (datadbs_chunk1)
Onspaces-A datadbs-P/home/Informix/datadbs_chunk1-o 0-s 15000

3. Delete the chunk
Onspaces-D datadbs-P/home/Informix/datadbs_chunk1-o 0

4. Delete dbspace (only when the dbspace to be deleted has no data)
Onspace-D datadbs

Export Database:
Use the dbexport tool to unload data into text and load it to other servers.
(1) To uninstall the text, follow these steps:
Register with Informix users
Dbexport cleardb-O workdir-SS
When the system prompts dbexport completed! Data has been detached.
Where:
-SS ensures that the database creation information or table creation information is retained, such as the log mode, initial extent size, lock mode, and dbspace of the table.
-O: Specifies the directory where the unmounted data is stored. The data is stored in the cleardb. Exp Directory, which contains cleardb. SQL and files such as *. unl,
The prompt information is stored in the dbexport. Out file.
(2) The steps for loading text are as follows:
Use Informix user registration to ensure that the database is in the on_line status, and there is no database with the same name on the server.
Dbimport cleardb-I workdir
When the system dbimport completed! The system prompts that the data has been loaded.
Where:
-I specifies where to load.

How can I detach a database to a tape device and load it on another server without damaging the database information (such as row-level locks?
1) The uninstall procedure is as follows:
Register with a DBA user
Insert the tape for storing data into the tape drive to ensure that the tape and tape drive are available in good condition.
Dbexport cleardb-T/dev/RMT/0 m-B 512 K-s 2048000 K-SS
When the system prompts dbexport completed! Data has been detached.
Where:
-SS ensures that the database creation information or table creation information is retained, such as the log mode, initial extent size, lockmode, and dbspace of the table
-T tape device/dev/RMT/0 m
-S tape capacity 2 GB
-Block B Size: KB
The prompt information is stored in the dbexport. Out file.
2) the loading steps are as follows:
Register with a DBA user
Place the tape that stores the detached data on the tape drive. confirm that the tape drive is normal and that the database system is in the on_line status. The server does not have a database with the same name.
$ Dbimport cleardb-T/dev/RMT/0 m-B 512 K-s 2048000 K
When the system dbimport completed prompts that the data has been loaded! The prompt information is stored in the dbimport. Out file.


Recreate the table:

1) dbschema-D dbname-T tabname-SS tabname. SQL/* put the table structure into tabname. SQL */
Edit this file and add a new region length at the end of the create table statement.
Currently, the tabname. SQL part that generates the table index is marked as a description statement, that is, "{}" is added before and after the index sentence. For example:
{Create index XL on customer (last_name); Create index X2 on customer (cust_nbr );}
It is best to generate an index later, because the table can be quickly loaded and the index can be generated in a continuous disk space to make it more effective.

2) Lock the table
Lock table tabname in exclusive mode;

3) use SQL unload to detach a table to a disk or tape.
Unload to file_or_tape_path select * From tabname
Unload to "/usr/data/unload/customer. unl" select * from customer;

Check the number of rows in the table (using the table/INFO command in SQL) to verify whether the uninstallation is successful. Compare the number with the message displayed in the SQL statement after loading.
Use this to verify the number of rows in file_or_tape_path:
WC-l file_or_tape_path

4) delete a table

5) Use dbacess to generate a table and run tabname. SQL. Do not generate indexes.

6) use SQL load from or dbload to reload the table
Load from file_or_tape_path insert into tabname;
Load Form "/usr/data/unload/customer. unl" insert into customer;
For the log database, the above loading statement may produce long transactions or "to unlock lock" errors. In this case, we recommend using utilities such as dbload.
Insert data into a table. The-N option of dbload forces commit work after a certain row is inserted (1000 rows are recommended ).
Check the number of rows in the table (using the table/INFO command in SQL) to verify whether the loading is successful.

7) to produce an index, run the tabname. SQL section marked as the description statement in the first step.

8) Update table statistics
Update statistics on tabname;

Syntax oninit [-S] [-I] [-p] [-y] Oninit changes the system from off-line mode to on-line mode Oninit-s changes the system from off-line mode to quiescent Mode Oninit-I initialization system Oninit-P does not search when the shared memory is initialized. It deletes the temporary table. Oninit-y automatically answers yes Oninit-V adds this option to display the oninit processing process Oninit -- enter this command for help The oninit command is used to change the operating mode of the system. The-I option is used to initialize the system's root dbspace. Note that once the root-dbspace is initialized, the entire database system is initialized. If you want to automatically start the dynamic server system when the computer is started, please start the file in the system (in many UNIX systems,/etc/rc) add the oninit command (without any options ). Onmode command Syntax: onmode [-K] [-M] [-S] [-u] [-y] Onmode-K: Execute shutdown immediately and change the system to off-line mode. Onmode-M changes the system from quiescent mode to on-line mode Onmode-s executes graceful Shutdown Onmode-u run immediate shutdwon Onmode-y automatically answers yes The onmode command is also used to change the running mode of a dynamic server. In addition to the above options, onmode has many options that are irrelevant to changing the system running mode. Use the onspaces command to create a data space Syntax: onspaces-C [-B] [-D] [-Z] [-M] [-O] [-p] [-S] [-T] -C: Create blobspace or dbspace -B blobspace name -D dbspace name -G page size blobpages size -Full path name and offset (Kb) of the m mirror image device) -O offset (KB) -P pathname: full path name of the chunk Device -S size dbspace size (KB) -T create temporary dbspace The onspaces command is used to create a data space, a temporary space, and a blobspace for storing BLOB data ). Type onspaces to obtain the online help of this command. Onstat-D or onstat-D can be used to view important information about the data space in the system. Including: The chunk status, idle, and the number of reads and writes per chunk. Multiple System spaces that may be included in the system, especially after data sharding, we recommend that you use the command line to create a data space. You can use the following command to create a data space: Onspaces-c-d datadbs1-o 0-P/dev/rrvol3-s 60000 You can create a temporary data space as follows: Onspaces-c-d tempdbs1-t-o 0-P/dev/rrvol5-s 80000 In the system, temporary data space is very important. Generally, multiple temporary data spaces should be distributed on independent physical devices. Use the onspaces command to delete a Data Space Add or delete chunks Syntax: onspaces-a-d [-M] [-O] [-p] -A spacename: dbspace added chunk -M pathname: full path name and offset of the image device (KB) -O offset: the offset of the master device (KB) -P pathname: full path name of the chunk Device -S size chunk size -D spacename: delete chunk -O offset chunk device offset (KB) Onspaces can not only create a data space, but also delete a data space, a temporary data space, or a space that stores BLOB data. When deleting a data space, you must first ensure that it is useless, that is, there is no database or table in the data space. To delete a data space, enter the following command: onspaces-D dbspace_name/blobspace_name The data space is initially composed of a chunk (first chunk). Once the space is exhausted, you must append the chunk to improve system performance, when you allocate a chunk to a data space, you need to calculate it to ensure that the size of the chunk can adapt to future needs. Otherwise, when you append a chunk, it is physically not necessarily adjacent to the previous chunk, resulting in increased data reading time. The requirements for computing space will be described in later sections. The onspaces command can be used to add or delete a chunk to or from a data space. In addition, the command can also perform the following tasks: Start an image, stop an image, or change the chunk status. For example, you can use the following command to add a chunk to the data space: Onspaces-a-d datadbs1-0 60002-P/dev/rrvol3-s 60000 You can delete a chunk from a data space as follows: Onspaces-D datadbs1-O 60002-P/dev/rrvol3-s 60000 Onparams command Syntax: onparams-a-d-P [-D] [-S] [-L] -A: added logical logs. -D dbspace: Specify the dbspace for storing logs. -S size: new logical log size (KB) -D. Delete logical logs. -L logid: Specify to delete a logical log. -P changes the physical log -D dbspace: dbspace name for storing new physical logs -S size physical log size (KB) The system automatically creates logical logs and physical logs in the root dbspace during initialization. In DBMS systems, especially in OLTP environments, database operations are very frequent and logs must record a large amount of information, therefore, it is best for users to distribute multiple log files on different devices. There is a very simple method: create logical logs according to the required size, and create a small physical log. After the system initialization is complete, move the physical logs to other devices. The details about how to determine the size of the required physical logs will be detailed in the following chapter. The onstat-l command can be used to identify all new logic logs in the system as. These logical logs are actually used only after the system is archived. There is a simple way to activate these logical logs: execute a "pseudo" archive. The procedure is as follows: Set the parameter tapedev to/dev/null and run ontape-s once. You can also run the onbar-F command. Because pseudo-archive does not actually archive system information, you must archive the system in a timely manner. You can delete a logical log only when it is actually unavailable. Using onstat-l, we can see that all idle logs are marked as F. If the logical log contains the information required for transaction rollback or quick recovery, the logical log cannot be deleted. Using the onstat-l command, we can see that the logs that accept the current transaction are marked as C. If the logical log contains the last checkpoint record, it cannot be deleted. Only when the checkpoint record is written to the next log and the previous log is backed up can the log be deleted. Using the onstat-l command, we can see that logs containing the last checkpoint record are marked as l. You can use the onmode-C command to forcibly write the checkpoint record until the last checkpoint record is written into the required log. Onstat Tool Displays information in the shared memory of a dynamic server. Involves a small number of disk I/O operations Will have minimal impact on system performance Provide valuable information about the system Command Line interactive monitoring system In this chapter, we will explain the various important options of onstat. Familiarize yourself with their usage. The onstat command is a very useful system real-time monitoring tool. This command reads information directly from the data structure of the shared memory and reports the current system status. Generally, the onstat command does not perform disk I/O operations or lock system resources. Therefore, it minimizes the impact on system performance. In short, onstat is an interactive system monitoring tool provided by Informix. Some common options of onstat: Onstat -- list all options Onstat-I settings enter Interaction Mode Onstat-display running mode and Database Engine status Onstat-G sub_option Option Onstat-r repeats every second Onstat-G Act displays all activity clues in the system Onstat-G ath-R 2 shows all activity clues in the system every two seconds System Monitoring page The sysmaster database is automatically created during system initialization. The database includes data dictionary information pointing to the system shared memory structure. Provides instantaneous system performance and status information Provides an SQL interface to learn Data Dictionary Information Allow administrators to automate System Management Used to execute repetitive monitoring tasks The System Monitoring Interface (SMI) is an SQL-based system tool. You can easily obtain system information through SMI. During initialization, the system automatically creates the sysmaster System database in the root dbapace. Each system includes a sysmaster database. Most of the tables in this database are virtual tables and are pointers to the data structure in the shared memory. SMI can be used to automatically monitor the system, especially when repeated tasks are executed. The sysmaster database is read-only. Therefore, you cannot perform insert update or delete operations on it. All users have the query permission for the database. After system initialization, you must check whether the sysmaster database is successfully established. Information about the sysmaster database is stored in the Message Log File (online. log) Please note that because all DDL statements will be recorded in the log, you must leave enough space in the log file to ensure that the sysmaster is required to be created. The sysmaster database mode is recorded in the $ informixdir/etc/sysmaster. SQL file. Oncheck command Check the data structure on the dynamic server disk Locate and repair damaged indexes and Data Disk I/O operations Some operations will be locked in the table during processing The oncheck command is used to repair damaged indexes and data pages on the disk. It can also be used to check and print the data structure of the system. Be careful when using oncheck, because some options of the change command will apply a shared lock to the table, for example: oncheck-Pt To prevent system corruption, the system checks the consistency of all data in the shared memory. Once a consistency error or index error is found on the data page, the system writes the problem and possible solution (using oncheck) to the Message Log File and returns the isam error code 105. If consistency error information is found, you should immediately shut down the system and run oncheck as prompted in the message log file. If oncheck fails, try to unload the table and reload the table. If the index is faulty, delete the index and recreate it. If all these steps fail, the system can only be restored based on the original system archive backup. The following command provides extend information: Oncheck-PE in onstat-L, the flag bit indicates the status of the logical log, A Indicates that logs that are not available are newly added. F indicates idle logs that can be used. Generally, logs are in this status only after 0 backups. Utable shows the used logical logs L indicates that the current log file contains a checkpoint C indicates that the current log file is being used. B Indicates the log file that has been backed up