Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 3: DBMS instances and storage objects

This tutorial is the third in a series of nine tutorials designed to help you become familiar with all the different aspects of IBM® Informix® Dynamic Server (IDS) and help you get ready for the IDS Fundamentals Certification exam. In this part, which corresponds with Part 3 of the exam, learn how to identify and connect to IBM Informix database servers and databases. Learn also how to create and configure database storage objects, and gain an understanding of system databases and system catalog tables.

Share:

Matthew J. Margan (mmargan@au1.ibm.com), Informix Advanced Support Engineer, IBM

Matthew Margan photoMatthew Margan is an Informix advanced database support engineer with the Informix Down Systems Support Team.



27 August 2009

Also available in Chinese

Before you start

About this series

This complimentary series of nine tutorials has been developed to help you prepare for the IBM Informix Dynamic Server 11.50 Fundamentals certification exam (555). This certification exam will test your knowledge of entry-level administration of IDS 11.50, including basic SQL (Structured Query Language), how to install IDS 11.50, how to create databases and database objects, security, transaction isolation, backup and recovery procedures, and data replication technologies and purposes. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam.

About this tutorial

In this tutorial, learn how to identify and connect to Informix database servers and databases. Learn also how to create and configure database storage objects, and gain an understanding of system databases and system catalog tables.


Identify and connect to Informix database servers and databases

A UNIX® or Windows® computer can have several IDS instances running on it simultaneously. An IDS instance, or database server, can have several databases within it.

To see details of the instances running on a computer, run the following command:

onstat –g dis

Listing 1 provides an example output from onstat –g dis. This output shows information about a single IDS instance.

Listing 1. Example output from onstat –g dis
Server        : mm11fc3hdr
Server Number : 78
Server Type   : IDS
Server Status : Up
Server Version: IBM Informix Dynamic Server Version 11.50.FC3
Shared Memory : 0xa000000
INFORMIXDIR   : /space/ifmx/11.50.FC3
ONCONFIG      : /space/ifmx/11.50.FC3/etc/onconfig.mm_HDR1
SQLHOSTS      : /space/ifmx/11.50.FC3/etc/sqlhosts
Host          : swanners

It is possible to connect to either a remote or a local database server.

A local connection occurs when the connecting application and the database server are on the same computer. A remote connection occurs when the connecting application and the database server are on different computers; the connection from the client to the server must be made over a network.

Local connections between a client application and a database server can be made in three ways:

  • Through a shared memory message system. In this case, the client application and the server attach to the same segment of shared memory. The application leaves messages for the server and picks up messages left by the server.
  • Through a stream-pipe connection. This is a local inter-process communication method that uses UNIX streams.
  • Through TCP/IP using the sockets or TLI programming interface. When the client application and the database server are on the same computer, IDS uses local loop back.

Remote connections between a client application and a database server can only be made through TCP/IP.

To communicate with TCP/IP, IDS supports two types of interfaces:

  • Sockets
  • TLI (Transport Layer Interface)

Check the release notes in the $INFORMIXDIR/release directory to find the interface supported by IDS on your platform. The network programming interface for Windows is WINSOC (sockets programming Windows interface).

To specify the method the application uses to connect to the database server, you must add sqlhosts information and also set some configuration parameters and environment variables.

When an application attempts a connection to a database server, some basic information is needed to make the connection. This information is held in the $INFORMIXDIR/etc/sqlhosts file in a UNIX environment. For Windows, the sqlhosts information is kept in the registry. The services file is: C:\WINNT\System32\drivers\etc\services.

The user running the application must set the INFORMIXSERVER environment variable to a key name that points to the entry in the sqlhosts file.

For a server connection, the key name is the same value as the DBSERVERNAME or DBSERVERALIASES configuration parameter found in the onconfig file. The key name can be any unique name within the sqlhosts file.

When the application finds the correct entry in the sqlhosts file, it has the information necessary to connect to the database server.

If the client application and database server are on different computers, you should include the sqlhosts file on both computers. This is necessary because the database server requires the sqlhosts file for initialization processing.

Clients can optionally set the pathname of the sqlhosts file with the INFORMIXSQLHOSTS environment variable to allow groups to have different views of the servers available for connection. For example, the IT staff might have an sqlhosts file with entries for both the production and development systems.

The sqlhosts file on UNIX/Linux

Figure 1 illustrates an example sqlhosts file for shared memory connections:

Figure 1. An example sqlhosts file for shared memory connections
An example sqlhosts file for shared memory connections

Dbservername is set to the value of the DBSERVERNAME configuration parameter or one of the values of the DBSERVERALIASES configuration parameter found in the onconfig configuration file. The DBSERVERNAME configuration parameter can only have one value. The DBSERVERALIASES configuration parameter can have up to 32 server names.

It also corresponds to the value of the INFORMIXSERVER environment variable set before starting the client application. The INFORMIXSERVER value must be specified in the DBSERVERNAME or the DBSERVERALIASES configuration parameter.

Nettype is set to onipcshm for a shared memory connection.

Hostname is set to the hostname of the local computer.

Servicename can be a unique or non-unique name within the sqlhosts file. The servicename is used as the name of a pointer into shared memory where data is exchanged. Usually, the same name is used for both the servicename and the dbservername.

The nettype column

The second column in the sqlhosts file is for the nettype. The nettype column contains information about the type of database server and how the connection is to be made.

Figure 2. The nettype column
The nettype column

The nettype consists of eight letters divided into three categories. The first two letters represent the database server product:

  • on or ol is used for IDS.
  • se is for Standard Engine, another IBM Informix database product.
  • dr is used with for Informix Enterprise Gateway with DRDA.

The second three letters refer to the programming interface used for the connection:

  • ipc is for Inter-Process Communication and is used for local connections only.
  • tli is for Transport Layer Interface, a programming interface for TCP/IP communication.
  • soc is for sockets, a programming interface for TCP/IP communication.

The last three letters refer to the specific protocol or IPC mechanism:

  • shm is for shared memory connection.
  • str is for the stream-pipe connection.
  • tcp is for the TCP/IP protocol.
  • spx is for the IPX/SPX protocol.
  • imc is for IBM Informix MaxConnect.

Table 1 provides some example sqlhosts file entries for various communication methods:

Table 1. sqlhosts file entries for various communication methods
Communication methoddbservernamenettypehostnameservicename
Stream pipessydney2onipcstrsydneysydney2
TCP/IP socketssydneysoconsoctcpsydneysydneysoc
TCP/IPsydneytcpontlitcpsydneysydneytcp

Windows – The sqlhosts registry

The sqlhosts registry contains information that enables a client application to find and connect to any Informix database server on the network. When you install the database server, the setup program creates the following key in the Windows registry: HKEY_LOCAL_MACHINE\SOFTWARE\INFORMIX\SQLHOSTS. The name of the database server is a key on the HKEY_LOCAL_MACHINE\SOFTWARE\INFORMIX\SQLHOSTS branch.

For each key (that is, each defined database server), the following sub-keys are provided:

  • HOST: The name of the computer where the database server resides.
  • OPTIONS: Provides additional flexibility in specifying connections.
  • PROTOCOL: Identifies the protocol used for connectivity.
  • SERVICE: Provides the detail required for the specified protocol to function.

Location of the sqlhosts registry:

When you install the database server, you can specify the name of the computer where you want to store the sqlhosts registry. You have two options:

  • The local computer where you are installing the database server
  • Another computer in the network that serves as a central, shared repository of sqlhosts information for multiple database servers in the network.

You can specify which machine holds the sqlhosts file for the database server by using the INFORMIXSQLHOSTS environment variable. If you specify a shared sqlhosts registry, you must set the INFORMIXSQLHOSTS environment variable on your local computer to the name of the Windows computer that stores the registry.

Editing or adding to the sqlhosts registry:

You can use the SetNet32 application to edit existing entries or add new entries to the sqlhosts registry:

  1. Launch SetNet32. If you cannot find SetNet32 from your Start menu, you can launch the application directly from %INFORMIXDIR%\bin\setnet32.exe.
  2. Click on the HOSTS tab.
  3. Enter or modify the information as required.
  4. Click on Apply to enter the information in the sqlhosts registry.
  5. Click on Make Default Server to set INFORMIXDIR to this database server for your user.

Note: This modifies the HKEY_CURRENT_USER\Software\Informix\Environment registry.

You can also use the registry editor to create or change an entry in the sqlhosts registry.

Connect to a local database

A local database is one that resides within an IDS instance running on the host you are logged in to.

You can connect to a database locally using the DB-Access utility that is built in to the server.

To connect to a local database:

  1. Log onto the server containing the IDS instance you wish to access.
  2. Set environment variables as you did in the previous section.
  3. Enter the command dbaccess to start the DB-Access utility. You will see the DB-Access top-level menu:
Listing 2. Start the DB-Access utility
DBACCESS:   Query-language  Connection  Database  Table  Session  Exit
Use SQL query language.

------------------------------------------------ Press CTRL-W for Help --------
  1. Use the left-right arrows to select the Query-language option. You will then see the Select Database sub-menu, where you can see all the databases that reside in this particular IDS instance:
Listing 3. Select Database sub-menu
SELECT DATABASE >>
Select a database with the Arrow Keys, or enter a name, then press Return.

------------------------------------------------ Press CTRL-W for Help --------

 stores_demo@mm11fc3hdr1

 sysadmin@mm11fc3hdr1

 sysha@mm11fc3hdr1

 sysmaster@mm11fc3hdr1

 syspgm4gl@mm11fc3hdr1

 sysuser@mm11fc3hdr1

 sysutils@mm11fc3hdr1
  1. Use the arrow keys to select the database you wish to access. In this case, there is only one user-created database (stores_demo). Select it.

    You can see the database name (stores_demo) and the IDS instance you are connected to (mm11fc3hdr1), as defined by the INFORMIXSERVER environment variable:

Listing 4. Database name and IDS instance
SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Enter new SQL statements using SQL editor.

----------- stores_demo@mm11fc3hdr1 ------------ Press CTRL-W for Help --------

By choosing from amongst the various menu options, you can perform activities in relation to this database including:

  • Viewing a list of tables within the database (Info option)
  • Dropping the database (Drop option)
  • Opening the vi editor to write and execute SQL

Connect to a remote database

A remote database is one that resides within an IDS instance running on a computer other than the computer you are logged in to.

Connecting with DB-Access

Suppose you are connected to a computer named swanners and you want to query the stores_demo database on a computer called millie that has a database server named tcp_1001. The sqlhosts file on the computer swanners must have an entry for the database server tcp_1001 that looks like this:

Table 2. sqlhosts entry
DbservernameNettypeHostnameServicename
tcp_1001ontlitcpmillie1906

Note: As an alternative to the servicename, you can enter the port number that corresponds to the dbservername in the /etc/services file.

From DB-Access, the syntax to query a database on another computer is: database@server:[owner.]table.

Listing 5. Example syntax to query a remote database in DB-Access session
SQL:   New  Run  Modify  Use-editor  Output  Choose  Save  Info  Drop  Exit
Run the current SQL statements.

----------------------- my_db2@mm11fc3hdr1 ----- Press CTRL-W for Help --------


select * from stores_demo@tcp_1001:state;

The SQL statement in Listing 5, above, will select from the state table in the stores_demo database on the server with dbservername tcp_1001.

Connecting to a remote database from a Windows client

You need to install drivers on the client to communicate with the IDS server. The easiest way to do this is to install IBM Informix Client SDK (CSDK) on the client. You can then test connectivity using the ILogin utility built into CSDK.

Here are example steps to install and test connectivity on a Windows XP client:

  1. Install CSDK on the client computer.
  2. Run the Setnet32 utility (installed with CSDK).
  3. Select the Environment tab.

    You will see a screen similar to that in Figure 3:

    Figure 3. Setnet32 – Environment tab
    Screenshot of Environment tab in Setnet32 utility, listing environment variables and options to edit, load, and save individual environment variables
  4. Ensure that the INFORMIXDIR environment variable is set to the location where CSDK is installed on the client computer.

    If necessary, you can edit INFORMIXDIR in this step. To do so, open the Control Panel, and select System > Advanced > Environment Variables.

  5. Ensure that the INFORMIXSERVER environment variable is set to a database server name that is a value for the DBSERVERNAME or DBSERVERALIASES configuration parameter in the onconfig file on the server. Ensure also that this name corresponds to a tcp connection (for example, ontlitcp) in the sqlhosts file on the server.

Table 3 includes sample sqlhosts entries on the server:

Table 3. Sample sqlhosts entries
DbservernameNettypeHostnameServicename
ids11uc2_cdr1tcpontlitcpSwannersids11uc2_cdr1tcp

If you have incorrectly set INFORMIXSERVER to a shared memory connection (such as ids11uc2_cdr1 in Table 3 above), you will get a 25596 error: The INFORMIXSERVER value is not listed in the sqlhosts file or the Registry.

  1. Add this same entry in the services file on the server and client. On a Windows XP client the services file is: C:\WINDOWS\system32\drivers\etc\services.

    So in this case, this file needs to contain the line:

    ids11uc2_cdr1tcp 	32012/tcp
  2. Enter the following fields on the Setnet 32 Server Information tab:
    • IBM Informix Server (ids11uc2_cdr1tcp, for example).
    • HostName: The computer on which the server resides (swanners, for example).
    • Protocolname: Any tcp connection protocol. The ontlitcp protocol is not an option in the drop-down list; however, onsoctcp works.
    • Service Name: Should correspond to the entry in the services file on the client and server (ids11uc2_cdr1tcp) or the corresponding port number.
    Figure 4. Setnet32 – Server Information tab
    Screenshot of Server Information tab in Setnet32 utility with editable fields for server information and options to make server the default or delete server
  3. Enter the following fields on the Host Information tab:
    • Current Host: (swanners, for example)
    • User Name: informix
    • Password Option: Password
    • Password: [password for informix]
    Figure 5. Setnet32 – Host Information tab
    Screenshot of Host Information tab in Setnet32 utility with editable fields for host information and option to delete host

You can now use the ILogin utility to test connectivity:

  1. Open ILogin, and choose File > Run. You will see a screen similar to that in Figure 6, where you can enter the following login parameters:
    • Server: ids11uc2_cdr1tcp
    • Stores Database: stores_demo
    Figure 6. Login Parameters
    Screenshot of login parameters with the following editable fields: server, hostname, servicename, protocolname, username, password, and stores database
  2. Click on OK. You should then see a list of customer records found, as shown in Figure 7:
    Figure 7. ILogin - Output
    Screenshot of ILogin output, which includes a list of customer records found; details of customers include number, first name, and last name

You have now confirmed that the client can connect to the database.

For more information on using the Setnet32 utility, refer to the manual "Configure IBM Informix Client products on Windows systems" (see Resources).

JDBC connection string

Listing 6 provides an example of a JDBC connection string required within your Java technology code to connect to an IDS database:

Listing 6. Example JDBC connection string
Connection conn = DriverManager.getConnection (
"jdbc:informix-       
sqli://millie:8003/test:INFORMIXSERVER=tcp_940;user=informix;password=mypassword");
  • millie is the hostname you are connecting to.
  • 8003 is the port.
  • tcp_940 is the database server name.
  • informix is the user you are connecting as.
  • mypassword is the password.

Create and configure database storage objects

IBM Informix Dynamic Server provides two data storage options:

  • A raw device, which is a character-special device that allows the database server to use unbuffered disk access.
  • A cooked file, which is a regular file that is managed by the operating system. While the database server controls the contents of the file, it must make I/O requests to the operating system.

Prepare a raw device

Perform the following steps to prepare a raw device for use by IDS:

  1. Create a new (or identify a free) partition on a disk.
  2. Set the file permissions to 660 (rw-rw----): chmod 660 device_name.
  3. Set the group of the file to informix: chgrp informix device_name.
  4. Set the owner of the file to informix: chown informix device_name.

To create a raw device, refer to your system administration manuals for your UNIX system.

If you are going to repartition an existing disk, it is recommended you archive the data on the disk before you unmount the device and repartition it.

Make note of the size of each raw device in kilobytes. You need to use this information when you create chunks.

To prepare the raw device for use in your database server, set the permissions, ownership, and group using the commands shown in Steps 2 through 4 above.

Note: Do not execute the mount command, which mounts the device and instructs the operating system to intercept input from and output to the device. Also, do not use the mkfs command, which constructs a file system on the device. If you run mkfs on a raw device that is currently used by IDS, the database information could be destroyed.

When using raw devices for chunks, use links to assign a more descriptive name to the device (for example, /dev/ifmx-raw-001). This makes it easier to remember the device names used for each chunk and, more importantly, allows you to redefine a link to a new device in case of a disk device failure.

Listing 7 provides an example of a session in which a raw device is prepared for use as a Dynamic Server chunk:

Listing 7. Example session of raw device preparation
$ ln /dev1/rxy0f /dev/informix-raw-001
or: ln -s /dev1/rxy0f /dev/informix-raw-001
$ chmod 660 /dev/informix-raw-001
$ chgrp Informix /dev/informix-raw-001
$ chown Informix /dev/informix-raw-001
$ ls -lg /dev/informix-raw-001
crw-rw---- 2 informix informix 9, 5 Jan 1 12:00 /dev/informix-raw-001

Create a cooked file

Perform the following step to create a cooked file for use by IDS:

  1. Set up a file to use: touch filename.
  2. Set the file permissions to 660 (rw-rw----): chmod 660 filename.
  3. Set the group of the file to informix: chgrp informix filename.
  4. Set the owner of the file to informix: chown informix filename.

It is not recommended to use UNIX files for your chunks, especially if your system can take advantage of kernel AIO. However, cooked files are very easy to set up without having to investigate the availability of disk devices. If you want to use a UNIX (cooked) file for your chunks, you must set up a file to be used for the chunk.

Use the command shown in Step 1 above, replacing filename with the name of the cooked file. Then, change the permissions and ownership of the file using the commands shown in Steps 2 through 4.

Listing 8 provides an example of a session in which a cooked file is created:

Listing 8. Example session of cooked file creation
$ touch /dev/informix-cooked-001
$ chmod 660 /dev/informix-cooked-001
$ chgrp informix /dev/informix-cooked-001
$ chown informix /dev/informix-cooked-001
$ ls -lg /dev/informix-cooked-001
-rw-rw---- 1 informix informix 0 Jan 1 12:00 /dev/informix-cooked-001

The length field, which appears after the group name, shows a value of 0 for the cooked file until the chunk is initialized.

You do not need to allocate space to the file at this point. When you assign the chunk to the server and specify the size to use for the chunk, the server allocates space for that chunk by increasing the size of the cooked file to the size you specify.

Disk component - Chunk

The server does not use the UNIX file system to do its disk space management; rather, it uses its own mechanisms for managing disk space—mechanisms that are much better suited for database management.

It is necessary to make physical space available to the server. This space is assigned in units called chunks.

A chunk is a contiguous unit of disk space that is assigned to the server to use; the server manages the use of space within that chunk. A chunk can be a UNIX raw device, or it can be a UNIX cooked file.

A chunk is uniquely identified by:

  • An absolute path name
  • An offset

It can be a:

  • Raw disk
  • Cooked file

It has a designated size in kilobytes, and it is organized internally by the server.

Figure 8. A chunk
A chunk

On Windows, a chunk is either an NTFS file or an allocation of raw disk space. It is organized internally by the server. We do not recommend using raw disk (unformatted partition) on Windows since it might be mistakenly formatted by someone not expecting the use of raw disk on a Windows system.

Disk component - Pages

When a chunk is assigned to the database server, it is broken down into smaller units called pages. The page is the basic unit of I/O for IDS. All data in a server is stored in pages. For example, if you were to store a row for a database table, the data for that row would be stored on a page. Pages have internal structure, and there are different types of pages.

A page is the minimum I/O that is read from or written to/from a disk. When data is read from the disk into a buffer in shared memory, the entire page on which that data is stored is read into the buffer.

The size of the page used for a server system prior to IDS 10.0 is determined when a port is made to a particular computer. The most common page size is 2KB, although some systems use a 4KB page size.

Since IDS Version 10, you can specify a non-default page size for new non-root dbspaces. The page size can range from 2K to 16K and must be a multiple of the default page size for the platform.

Figure 9. A page
A page

Disk component - Dbspaces

A dbspace is a logical collection of chunks. Each dbspace must have at least one chunk assigned to it initially.

Dbspaces can have as many chunks assigned to them as necessary. If you run out of space in a particular dbspace (because all the chunks assigned to it are full), you can add additional chunks to it.

Figure 10. A dbspace
A dbspace

Databases and tables can be created in particular dbspaces. This means that the table or database can only grow as large as the available space in that dbspace. You cannot control which chunks within any given database or table will be created. If you want to be sure that tables or databases are created on a specific physical device, you should assign only chunks that reside on that device to the dbspace.

Note: Every server system must have at least one dbspace—the root dbspace. This is where all the important system information that controls the server is located.

Disk component - Tblspaces

A tblspace is the logical collection of all the pages allocated for a given table or, if the table is fragmented across dbspaces, a fragment of the table located in a dbspace.

The space represented by a tblspace is not necessarily contiguous; pages can be spread out on a single chunk, or pages for a table can be on different chunks.

Figure 11. A tblspace
A tblspace

You can think of dbspaces and tblspaces as logical groupings of physical space.

  • A dbspace is a logical grouping of physical chunks. The chunks can be on different disks, even though they are part of the same dbspace.
  • A tblspace is a logical grouping of extents. The extents within a tblspace can be on different chunks (and therefore different disks).
Figure 12. Logical groupings: Dbspaces and tblspaces
Logical groupings: Dbspaces and tblspaces

Special data types – Simple large objects

Simple large objects are streams of bytes of arbitrary value and length. A simple large object might be a digitized image or sound, an object module or a source code file.

Anything that you can store in a file system of a computer can be stored in a simple large object. The theoretical limit to its size is 2GB; this size is based on the highest value that can be held in a 4-byte integer.

There are two types of simple large objects: TEXT and BYTE. The TEXT data type is used for the storage of printable ASCII text, such as source code and scripts. The BYTE data type is used for storing any kind of binary data, such as saved spreadsheets, program load modules, and digitized images or sound.

Disk component – Blobspaces

A blobspace is a special type of dbspace that is used to store simple large objects. It has the following characteristics:

  • It is a pool of disk that can only be used for storing simple large objects.
  • It must have at least one chunk.
  • It can have many chunks.
  • It can contain simple large objects from any columns, any table, and any database.
Figure 13. Blobspaces
Blobspaces

Special data type – Smart large objects

Smart large objects Include:

  • CLOB and BLOB built-in types: The CLOB and BLOB types are user-defined data types for storing a large amount of data. These types are created automatically for each database and are stored in the sysxtdtypes catalog.
  • Third party indexes: Third party applications might use smart large objects as a place to store data for an index. This index would only be manipulated by the DataBlade module that created it.
  • User-defined data types (UDTs): Smart large objects provide an ideal storage method for large user-defined data types.

Disk component – Sbspaces

An sbspace is a logical collection of chunks. It has the following characteristics:

  • It is a pool of disk space that tables created by the server can use to store smart large objects.
  • It must have at least one chunk.
  • It can have many chunks.
Figure 14. Sbspaces
Sbspaces

Like other storage spaces in Informix Dynamic Server, sbspaces are comprised of one or more chunks. However, at a minimum, the first chunk of an sbspace has a more complex architecture. This architecture is described in a later module.

Sbspaces are used to store smart large objects sometimes called smart LOs or smart blobs.

Create storage objects using the command line

The onspaces utility can be used to create a dbspace, blobspace, sbspace, or temporary dbspace in a server from the system command line.

Creating a dbspace with onspaces

Table 4 lists the arguments to create dbspace with the onspaces utility:

Table 4. Arguments to create a dbspace with the onspaces utility
ArgumentAction
-cCreate a dbspace
-d spacenameThe name of the dbspace to be created
-k pagesizeNon-default page size for the new dbspace (must be between 2K and 16K, and be a multiple of the default page size)
-m pathname offsetMirror pathname and offset
-o offsetThe offset into the device in kilobytes of the initial chunk
-p pathnameDisk partition or device name of the initial chunk
-s sizeThe size of the initial chunk in kilobytes
-tIndicates that the dbspace created is a temporary dbspace

The following example command creates a 2GB dbspace named dbspace3 with an offset of 100,000 kilobytes for the primary chunk:

onspaces -c -d dbspace3 -p /dev/rdsk/device1 -o 100000 -s 2000000

To create an 800,000KB temporary dbspace named tempdbs1 with an offset of 100,000 kilobytes, you can use the following command:

onspaces -c -t -d tempdbs1 -p /dev/rdsk/device9
-o 100000 -s 800000

Creating a blobspace with onspaces

Table 5 lists the arguments to create a blobspace with the onspaces utility:

Table 5. Arguments to create a blobspace with the onspaces utility
ArgumentAction
-c Create a blobspace or dbspace
-b spacename The name of the blobspace to be created
-g blobpagesize Blobpage size in number of disk pages
-m pathname offset Mirror pathname and offset
-o offset Offset into the initial chunk in kilobytes
-p pathname Device or filename of the initial chunk
-s size Size of the initial chunk in kilobytes

The following example command creates a 1GB blobspace named blobspace2 with an offset of 100,000KB and a blobpage size of 1000KB (2K system page size):

onspaces -c -b blobspace2 -g 500 -p /dev/rdsk/device8
-o 100000 -s 1000000

Creating an sbspace with onspaces

Table 6 lists the arguments to create an sbspace with the onspaces utility are:

Table 6. Arguments to create an sbspace with the onspaces utility
ArgumentAction
-c Create an sbspace
-S spacename The name of the sbspace to be created
-m pathname offset Mirror pathname and offset
-o offset Offset into the initial chunk in kilobytes
-t The sbspace is created as a temporary sbspace
-p pathname Device or filename of the initial chunk
-s size Size of the initial chunk in kilobytes
-Ms metasize Size of the sbspace metadata area in kilobytes
-Mo metaoffset Offset of the metadata area into the sbspace in kilobytes
-Df options Specify various default storage characteristics and other attributes for smart large objects to be stored in the sbspace

The following example command creates a 5GB mirrored sbspace named sbspace4 with an offset of 300,000KB for both the primary and mirrored chunks, a metadata size of 150,000KB with a 10,000KB offset, and an expected average smart blob size of 32KB:

onspaces -c -S sbspace4 -p /dev/rdsk/device5 -o
300000 -s 5000000 -m /dev/rdsk/device6 300000
-Ms 150000 -Mo 10000 -Df "AVG_LO_SIZE=32"

Dropping spaces with onspaces

The onspaces utility can be used to drop a dbspace, temporary dbspace, blobspace, or sbspace from the system command line.

To drop a dbspace, blobspace, or sbspace, the space should be unused.

Before you drop a dbspace, you must first drop all databases and tables previously created in that dbspace.

Before you drop a blobspace, you must drop all tables that have a TEXT or BYTE column that references the blobspace.

To drop the dbspace dbspace3, issue the following command:

onspaces -d dbspace3

To drop an sbspace that contains data, use the -f (force) option:

onspaces -d sbspace2 -f

Adding a chunk to a dbspace or blobspace

You can use the onspaces to add a chunk to a dbspace or blobspace. Table 7 lists the arguments to add a chunk to a dbspace or blobspace with the onspaces utility:

Table 7. Arguments to add a chunk to a dbspace or blobspace with the onspaces utility
ArgumentAction
-a spacename Add a chunk to the specified dbspace or blobspace
-m pathname offset Mirror pathname and offset
-o offset Offset into the chunk in kilobytes
-p pathname Device or filename of the chunk
-s size Size of the chunk in kilobytes

The following example command adds a 5GB mirrored chunk to dbspace1 with a 500,000KB offset for the primary chunk and a 200,000KB offset for the mirrored chunk:

onspaces -a dbspace1 -p /dev/rdsk/device2 -o 500000
-s 5000000 -m /dev/rdsk/device5 200000

Adding a chunk to an sbspace

Table 8 lists the arguments to add a chunk to an sbspace with the onspaces utility:

Table 8. Arguments to add a chunk to an sbspace with the onspaces utility
ArgumentAction
-a spacename Add a chunk to the specified sbspace
-m pathname offset Mirror pathname and offset
-o offset Offset into the new chunk in kilobytes
-p pathname Device or filename of the new chunk
-s size Size of the new chunk in kilobytes
-Ms metasize Size of the sbspace metadata area to allocate in the new chunk in kilobytes
-Mo metaoffset Offset of the metadata area into the new chunk in kilobytes
-U Indicate that the new chunk is to contain only user data

The following example command adds a 7GB mirrored chunk to the sbspace named sbspace6 with an offset of 50,000KB for both the primary and mirrored chunks and a metadata size of 10,000KB with a 5000KB offset:

onspaces -a sbspace6 -p /dev/rdsk/chunk7 -o 50000 -s
7000000 -m /dev/rdsk/chunk6 50000 -Ms 10000 -Mo 5000

To use the chunk for user data only, specify the -U option (and omit the -Ms or -Mo options).

Dropping a chunk with onspaces

The onspaces utility can be used to drop a chunk in a dbspace, sbspace, or blobspace. Table 9 lists the arguments to drop a chunk in a storage space with the onspaces utility:

Table 9. Arguments to drop a chunk in a storage space with the onspaces utility
ArgumentAction
-d space_name Drop a chunk to the specified dbspace, blobspace, or sbspace
-o offset Offset into the chunk in kilobytes
-p pathname Pathname of the chunk device or file

You can use onspaces to drop any non-initial chunk of a dbspace, sbspace, or blobspace.

The following example command drops a chunk from dbspace2 with an offset of 15,000KB:

onspaces -d dbspace2 -p /dev/rdsk/chunk3 -o 15000

Note that you cannot drop the initial chunk of a dbspace, sbspace, or blobspace this way. You must drop the storage space itself.

Renaming dbspaces

You might want to rename standard dbspaces if you are reorganizing data in an existing dbspace and see a need to change the dbspace name. The rename dbspace operation only changes the dbspace name; it does not reorganize data.

The server must be in quiescent mode when you rename a dbspace.

You cannot rename a critical dbspace, such as the root dbspace or a dbspace that contains either the physical or logical logs.

You should perform a level-0 archive after renaming a dbspace.

You can use the following syntax to rename a dbspace:

onspaces -ren old_dbspacename -new new_dbspacename

Manage spaces with the OpenAdmin Tool

You can use the OpenAdmin Tool for IDS (OAT) to manage storage spaces.

Adding a dbspace, blobspace, or sbspace with OAT

You can use OAT to add a dbspace, temporary dbspace, blobspace, or sbspace.

To do so, navigate to Space Administration > DBSpaces. Enter the space name, path, offset, size, type (DBSpace, Temp DBSpace, Blobspace, or SBSpace), and page size, and then click on Create:

Figure 15. Adding a dbspace with OAT
Adding a dbspace with OAT

(View a larger version of Figure 15.)

Adding a chunk with OAT

You can add a chunk to an existing dbspace, blobspace, or sbspace with OAT.

To do so, navigate to Space Administration > DBSpaces. Click on the name of the dbspace, blobspace, or sbspace to which you want to add a chunk. Click on the Admin tab, where you will then see options to add a chunk:

Figure 16. Adding a chunk with OAT
Adding a chunk with OAT

(View a larger version of Figure 16.)

Dropping a dbspace with OAT

Navigate to Space Administration > DBSpaces. Click on the name of the dbspace, blobspace, or sbspace that you want to drop. Click on the Admin tab, where you will see an option to drop the space:

Figure 17. Dropping a space with OAT
Dropping a space with OAT

(View a larger version of Figure 17.)

The OpenAdmin Tool is an open source product and can be downloaded online (see Resources).

Manage spaces with the SQL Administration API

The SQL administration API allows you to use the built-in admin() and task() functions to remotely accomplish administrative tasks through EXECUTE FUNCTION SQL statements that emulate various IDS administrative command-line utilities.

The task() and admin() functions provide the same functionality; they differ only in their return code. The task() function returns a string that describes the results of the command. The admin() function returns an integer.

Using the admin() function to create a dbspace

You can use the admin() function to create a dbspace.

The following example command creates a dbspace called mydbspace of size 30000KB and an offset into the chunk of zero:

execute function
admin("create dbspace","mydbspace","/space/ifmx/11.50.UC5_CDR1/my_chunk",
"30000","0");

Using the admin() function to create a blobspace

You can use the admin() function to create a blobspace.

The following example command creates a blobspace called myblobspace of size 10,000KB, zero offset, and a blob page size of four pages:

execute function 
admin("create blobspace","myblobspace","/space/ifmx/11.50.UC5_CDR1/my_blobchunk",
"10000","0","4");

Using the admin() function to create an sbspace

You can use the admin() function to create an sbspace.

The following example command creates an sbspace called mysbspace of size 15,000KB and an offset of 100KB into the chunk:

execute function 
admin("create sbspace","mysbspace","/space/ifmx/11.50.UC5_CDR1/my_sbchunk",
"15000","100");

Using the admin() function to add a chunk to a dbspace, blobspace, or sbspace

You can use the admin() function to add a chunk to a dbspace, blobspace, or sbspace.

The following restrictions apply:

  • The size of the chunk must be equal to or greater than 1000KB and a multiple of the page size.
  • The starting offset plus the chunk size cannot exceed the maximum chunk size.
  • The maximum offset is 4TB.

The following example command adds a chunk to the dbspace called data1space of size 10,000KB at zero offset:

execute function 
admin("add chunk","data1space","/space/ifmx/11.50.UC5_CDR1/datachunk2","10000","0");

Using the admin() function to drop a chunk, dbspace, blobspace, or sbspace

You can use the admin() function to drop a chunk, dbspace, blobspace, or sbspace.

The following example command drops the blobspace called myblobspace:

execute function admin("drop blobspace","myblobspace");

The following example command drops just one chunk with a pathname of /space/ifmx/11.50.UC5_CDR1/datachunk2 and offset of zero from the dbspace named data1space:

execute function 
admin("drop chunk","data1space","/space/ifmx/11.50.UC5_CDR1/datachunk2","0");

System databases and system catalog tables

The first time the database server is brought online, the sysmaster, sysutils, sysuser, and sysadmin databases are built.

These are system or internal databases used by IDS.

These databases contain system catalog tables as do user-created databases.

System catalog tables

System catalog tables track database objects, such as:

  • Tables, views, sequences, synonyms, and sequence objects
  • Columns, constraints, indexes, and fragments
  • Triggers
  • Procedures, functions, routines, and associated messages
  • Authorized users and privileges
  • User-defined routines
  • Data types and casts
  • Aggregate functions
  • Access methods and operator classes
  • Inheritance relationships
  • External optimizer directives

One of the system catalog tables within a database is the systables table. This table lists all tables within the database. Each table listed in the systables table is uniquely identified by a tabid. System catalog tables each have a tabid that is less than 99.

To view all system catalog tables within a database, you can run the following SQL statement:

database stores_demo;
select * from systables where tabid < 99;

The sysadmin, sysutils, and sysuser databases

The sysadmin database contains tables that store task properties. It allows a database administrator to schedule the running of administrative tasks.

The sysutils database contains tables used by the ON-Bar backup and restore utility.

The sysuser database is used for PAM (Pluggable Authentication Module) authentication in server-to-server communication.

The sysmaster database

If the sysmaster database cannot be created, the database server attempts to create it every time it is brought online.

The sysmaster database contains data dictionary information that points to shared memory structures. The tables in the sysmaster database are called SMI (system-monitoring interface) tables.

The system-monitoring interface (SMI) tables are special tables managed by the database server that contain dynamic information about the state of the database server.

Most of the SMI tables do not hold any data; instead, the data dictionary structures for that table point to structures in shared memory.

When a SELECT statement is executed on a regular table, the server reads the data dictionary information for the table to find the partition number and other information about the table. Then it accesses the data from disk if it is not in the buffer pool.

When a SELECT statement is executed on an SMI table, the server still reads the data dictionary information for the table listed in the SELECT statement. The SMI tables have a special partition number (the dbspace number within the partition number is 0). When the server detects the special partition number, it knows to read a specific set of data in shared memory to satisfy the query.

Because the SELECT statement is accessing real-time data in shared memory, the data between one SMI table and another might not be synchronized.

All users have permission to query the supported tables in the sysmaster database, except for the tables used for audit operations.

The following restrictions apply when using SMI tables:

  • You cannot lock the non-permanent SMI tables or use isolation levels. Because these tables are just shared-memory structures, the traditional SQL locking mechanisms are ineffective (and not desired).
  • INSERT, UPDATE, and DELETE statements are not allowed against non-permanent SMI tables.
  • The dbschema and dbexport utilities cannot be used for the sysmaster database. You will receive the following error:
    Database has pseudo tables - can't build schema.
  • The use of rowid in a SELECT statement is not relevant and it returns inconsistent results.

Sysmaster tables

The sysmaster database consists of over 50 tables. IBM Informix supports and documents only a few of these tables and some of the views that use these tables. For your protection, use only the supported tables and views in your queries, as the unsupported tables could change between releases. The supported tables and views are:

  • The sysdatabases table, which lists databases, owners, and database characteristics.
  • The systabnames table, which contains the names of all tables in the server.
  • The syscheckpoint and sysckptinfo tables, which are new in Version 11 and list checkpoint information.
  • The syslogs view, which contains information about the logical logs. You can use syslogs to determine if the logs need to be backed up.
  • The sysdbspaces view, which contains information about dbspaces.
  • The syschunks view contains the chunks in the server. The nfree column shows the number of pages in the chunk that are free.
  • The syslocks view, which lists all active locks.
  • The sysvpprof view, which contains all the active virtual processors.
  • The syssessions view, which lists information about each session.
  • The syssesprof view, which contains more information about each session.
  • The sysextents view, which lists extents allocated in the server.
  • The syschkio view, which contains I/O statistics by chunk.
  • The sysptprof view, which lists information about the tblspaces at any one point in time. Only tables currently being used are listed in this view. When the last user closes the table, the tblspace structure in shared memory is freed, and, subsequently, any profile statistics are lost.
  • The sysprofile view, which lists certain events in the server, such as disk reads, disk writes, roll backs, checkpoints, and so on. Each row contains one profiled event and its value.
  • The sysadtinfo table, which contains information about the auditing configuration for the server. You must be user informix to retrieve information from this table.
  • The sysaudit table, which contains the hexadecimal representation of each defined audit mask. To list, modify, or add an audit mask, you must use the onaudit utility. You must be user informix to retrieve information from the sysaudit table.
  • The sysconfig table, which describes the effective, original, and default values of the configuration parameters.
  • The sysdri table, which provides information on the data-replication status of the database server.
  • The sysseswts table, which provides information on the amount of time users wait for various database objects.

Database logging mode

IDS uses logical logs to record data manipulation language (DML) entries (INSERT, UPDATE, DELETE) for logged databases, as well as data definition language (DDL) statements and checkpoint activity for all databases.

If you do not enable database logging, the server cannot fully recover the database in the event of a failure, and you cannot use transactions.

Using the ondblog utility to change the logging mode of a database

You can use the ondblog utility to change the logging mode for one or more databases.

If you are changing the logging mode of a database, you must perform a level-0 backup before the change takes effect.

Use the following command to change the buffering status on the stores7 logged database:

ondblog unbuf stores7
ondblog buf stores7

To end logging, use the following command:

ondblog nolog stores7

To end logging for a list of databases in the file "mydbfile", use the following command:

ondblog nolog -f mydbfile

Use the following command to make a database ANSI-compliant:

ondblog ansi stores7

Using the ontape utility to change the logging mode of a database

You can add logging to a database with ontape at the same time that you create a level-0 backup.

For example, to add buffered logging to a database called stores_demo with ontape, execute the following command:

ontape -s -B stores_demo

To add unbuffered logging to a database called stores_demo with ontape, execute the following command:

ontape -s -U stores_demo

In addition to turning on transaction logging, these commands create full-system storage-space backups. When ontape prompts you for a backup level, specify a level-0 backup.

Note: With ontape, you must perform a level-0 backup of all storage spaces.

To end logging for a database called stores_demo with ontape, execute the following command:

ontape -N stores_demo

To change the buffering mode from buffered to unbuffered logging on a database called stores_demo using ontape without creating a storage-space backup, execute the following command:

ontape -U stores_demo

To change the buffering mode from unbuffered to buffered logging on a database called stores_demo using ontape without creating a storage-space backup, execute the following command:

ontape -B stores_demo

To make a database called stores_demo, which already uses transaction logging (either unbuffered or buffered), into an ANSI-compliant database with ontape, execute the following command:

ontape -A stores_demo

To make a database called stores_demo, which does not already use transaction logging, into an ANSI-compliant database with ontape, execute the following command:

ontape -s -A stores_demo

In addition to making a database ANSI compliant, this command also creates a storage-space backup at the same time. Specify a level-0 backup when you are prompted for a level.

Note: After you change the logging mode to ANSI-compliant, you cannot easily change it again. To change the logging mode of ANSI-compliant databases, unload the data, re-create the database with the new logging mode, and reload the data.

Specify a logging mode when you create a database

To create a database and specify a logging mode, use the CREATE DATABASE statement.

Listing 9 provides the syntax:

Listing 9. Syntax to create a database and specify a logging mode
>>-CREATE DATABASE--database--+-------------+------------------->
                              '-IN--dbspace-'   

>--+-----------------------------+-----------------------------><
   '-WITH--+-+----------+--LOG-+-'   
           | '-BUFFERED-'      |     
           '-LOG MODE ANSI-----'

The following example command creates a database called my_db in dbspace2 with unbuffered logging:

create database my_db in dbspace2 with log;

The command in Listing 10 verifies the logging status of the database and queries the sysdatabases table in the sysmaster database:

Listing 10. Verify the logging status by querying the sysdatabases table
database sysmaster;
select * from sysdatabases where name='my_db';

name         my_db
partnum      1049131
owner        informix
created      07/20/2009
is_logging   1
is_buff_log  0
is_ansi      0
is_nls       0
flags        -12287

Conclusion

The IBM Informix Dynamic Server Information Centre has searchable documentation on all the topics discussed in this tutorial (see Resources for a link). .

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=423329
ArticleTitle=Informix Dynamic Server 11.50 Fundamentals Exam 555 certification preparation, Part 3: DBMS instances and storage objects
publish-date=08272009