Interaction between shell scripts and Informix

In Linux/Unix, some programs are command-line or service programs. Generally, there is a configuration file to describe their running rules. These rules are usually stored in the database, maintained by other applications. This involves a question: how to get the latest rules from the database to drive the background program. After the background program ends, the generated results sometimes need to be written back to the database for other applications to read.

By reading this article, you can understand some common techniques and methods for interacting with shell scripts and Informix to implement functions such as querying, adding, deleting, and modifying data, finally, information interaction between databases and background programs is realized.

1. Brief Introduction to Informix

Informix is a family of relational database management systems (RDBMS) developed by IBM. As an integrated solution, it is positioned as the flagship Data Service System of IBM online transaction processing (OLTP. IBM has long-term plans for both INFORMIX and DB2. The two database products draw on each other's technical advantages. The latest version is Informix 11.7, which will continue to provide strength to IBM Informix over the next decade. Its engine effectively addresses OLTP, this makes it possible to better support the decision-making support applications of enterprises and partners of all sizes.

2. Brief Introduction to Linux/Unix shell

Linux/Unix shell is also called the command line interface. It is a scripting language used to control the system. It is a traditional user-computer interaction interface in Linux/UNIX operating systems. You can directly enter commands to execute various tasks, hiding the lower-layer details of the operating system.

Main functions of shell: Command Execution, input/output redirection, Environment Control, background processing, shell script.

3. Basic Methods for Shell-Informix Interaction

Shell calls dbaccess to execute various SQL statements on Informix, and CALLS dbload to load files to the database.

3.1. Connect to the database through dbaccess

Dbaccess provides a user interface for inputting, executing, and debugging Structured Query Language (SQL) Statements and stored procedure language (SPL) routines. Assume that the online database server contains a database named mystores. To make the mystores database the current database, you can use db2access mystores to start DB-access. you can also run the dbaccess // xyz/newstores command to connect to the newstores database on the XYZ Database Server (not online ).

3.2. Run the SQL statement through dbaccess

When dbaccess is called from the command line, you can specify a database as the current database, execute a file that contains one or more SQL statements, and display multiple levels of error information.

Example 1: dbaccess test insert. SQL

Run the SQL statement command in the insert. SQL file in the test database.

Example 2: dbaccess-insert. SQL

Indicates the SQL statement command in the execution file of the database specified in the insert. SQL file.

After the following function is called, dbaccess is called to execute related SQL statements and output the results to the standard output.

Listing 1

Executesqlselect ()

{

Sqltxt = "$1 ";

Pre_ SQL = "output to pipe cat without headings ";

Sqltxt = $ pre_ SQL "$ sqltxt ";

Echo "$ sqltxt" | dbaccess Test 2>/dev/NULL;

}

In the code in Listing 1, $1 indicates the 1st parameters of the function. Here, the 1st parameters are the SQL statements to be executed; the code in the pre_ SQL variable is used to block the running log information output during dbaccess execution. If this setting is not added, the function execution result is not affected, but many running logs of dbaccess are displayed during execution.

Echo "$ sqltxt" | dbaccess Test 2>/dev/null

Connect to the test database using the dbaccess command, execute the $ sqltxt statement, and output the error to/dev/null.

The preceding function is called in shell as follows:

User_name = 'executesqlselect "select user_name from t_test ;"';

3.3. Use shell to call dbaccess to export records

After the following function is called, The passed SQL statement is executed and the result is exported to a file.

Listing 2

Executesqlunloadfile ()

{

Sqltxt = "$1 ";

Unload_file = "$2 ";

Temp_file = "$3 ";

Pre_ SQL = "unload to $ unload_file delimiter ','";

Post_ SQL = "";

Echo $ pre_ SQL> $ temp_file;

Echo "$ sqltxt" >>$ temp_file;

Echo $ post_ SQL >>$ temp_file;

Dbaccess testdb <$ temp_file 2>/dev/NULL;

Rm-F $ temp_file;

}

In the code in Listing 2, $1, $2, and $3 indicate the 1st, 2nd, and 3rd parameters of the function.

The 1st parameters are SQL statements.

The 2nd parameters are output file names.

The first parameter is the temporary file name (the file will be deleted after execution ).

Unload to $ unload_file delimiter ',' indicates the file whose file name is $ unload_file during record export. The delimiter between records is expressed by commas.

$ Temp_file is a temporary file used to save SQL statements. Its content will be deleted at the end of function execution.

Dbaccess testdb <$ temp_file 2>/dev/null indicates to use the dbaccess command to connect to the testdb database. Then, execute the SQL statement in the $ temp_file file and output the error to/dev/null.

The preceding function is called in shell as follows:

Executesqlunloadfile "select C1, C2 from t_test" "t_test.dat" "t_test.tmp ";

3.4. Use dbload to import files

Dbload transfers data from up to one ASCII file to one or more existing tables.

Syntax: dbload [-D dbname] [-C cfilename] [-l logfile] [-e errnum] [-N nnum] [-I inum] [-S] [-p] [-R] [-K] [-X].

The main options are described as follows:

-D dbname: Specifies the database name for receiving data.

-C cfilename specifies the file name of the specified dbload command file

-L logfile: Specifies the file name of the error log file.

-R allows other users to modify table data during data loading (the table is not blocked during data loading)

-S indicates that dbload checks the statement syntax in the command file without inserting data.

-I inum indicates that dbload ignores the specified number of rows.

-N nnum indicates that dbload submits an operation after inserting a specified number of new rows

-E errnum specifies the number of bad lines that can be read before dbload is aborted.

-P if the number of bad rows exceeds the limit, a prompt is displayed. Please specify a solution.

For example, dbload-D testdb-C load. CTL-N 10000-l error. log. This command loads data to the testdb database according to the instructions of the load. CTL control file. Each 10000 records are submitted, and the error data is written to the error. log file. The following is the content of the load. CTL control file.

Listing 3

File CRM. dat delimiter '| '3;

Insert into data_crm_list;

The content in listing 3 indicates loading the CRM. dat data file to the data_crm_list table. The Delimiter is '|' and the number of data fields is 3.

4. Shell and Informix interaction instances
Through the introduction in Chapter 3, the reader has basically understood the interaction between Shell and Informix. In this chapter, the author will show the interaction process between Shell and Informix through a specific instance.

4.1. scenario overview

Script Name: Test. Sh

Demo steps:

A: create two tables in Informix: t_test and t_test2;

T_test table structure:

Create Table t_test

(

User_code varchar (20 ),

User_name varchar (20 ),

User_desc varchar (50)

) T_test2 table structure:

Create Table t_test2

(

User_code varchar (20 ),

User_name varchar (20 ),

User_desc varchar (50)

) B: insert two records in t_test;

Insert into t_test values ('20170101', 'test', 'This is a test ')

Insert into t_test values ('20170101', 'Guess ', 'This is a test') C: run the test. Sh script on the Linux terminal.

Running result:

A: The script reads two records from the t_test table and exports them as the file t_test.dat;

B: The t_test.dat file is imported into the t_test2 table. At this time, the number of t_test2 records is the same as that of t_test.

4.2. complete code and comments

#! /Usr/bin/ENV bash

Dbtool = "dbaccess"

Dbconnt = "test"

# Function Description: Execute the SQL statement and export the result to a file.

# Parameter description:

# The 1st parameters are SQL statements.

# The 2nd parameters are output file names

# The third parameter is the temporary file name (it will be deleted after execution)

Executesqlunloadfile ()

{

Sqltxt = "$1 ";

Unload_file = "$2 ";

Temp_file = "$3 ";

Pre_ SQL = "Set lock mode to wait 10; unload to $ unload_file delimiter ','";

Post_ SQL = "";

Echo $ pre_ SQL> $ temp_file;

Echo "$ sqltxt" >>$ temp_file;

Echo $ post_ SQL >>$ temp_file;

$ Dbtool $ dbconnt <$ temp_file 2>/dev/NULL;

Rm-F $ temp_file;

}

# Function Description: Execute SQL

# Parameter description:

# The 1st parameters are SQL statements.

Executesqlselect ()

{

Sqltxt = "$1 ";

Pre_ SQL = "Set lock mode to wait 10; output to pipe cat without headings ";

Sqltxt = $ pre_ SQL "$ sqltxt ";

Echo "$ sqltxt" | $ dbtool $ dbconnt 2>/dev/NULL;

}

# Function Description: remove the blank before and after Variables

Trim ()

{

Echo $1 | SED's/^ \ + // '| SED's/\ + $ //'

}

# Function Description: obtains the value of a field in the specified record of t_test.

Get_username_bycode ()

{

User_code = "$1 ";

Execsql = "select user_name

From t_test

Where user_code = "$ user_code ";";

Echo $ execsql

User_name = 'executesqlselect "$ execsql "';

User_name = 'trim $ user_name ';

Echo "user_name: [" $ user_name "]";

}

# Function Description: obtain all records in t_test and export them as the file t_test.dat.

Get_all_username ()

{

Execsql = "select user_code, user_name, user_desc

From t_test ;";

Echo $ execsql

Executesqlunloadfile "$ execsql" "t_test.dat" "username. tmp ";

}

# Function Description: generate the warehouse receiving configuration file

# Parameter description:

# The first parameter is the name of the table to be imported into the database.

# The first parameter is the name of the file to be checked in.

# The 3rd parameters are file delimiters

# 4th parameters are the number of file Columns

Make_loader ()

{

Table_name = $1

Data_name = $2

SEP = $3

Tab_num = $4

Echo "file \" $ data_name \ "delimiter \" $ Sep \ "$ tab_num;"> $ ctl_file;

Echo "insert into $ table_name;" >>$ ctl_file;

}

# Function Description: import data to the table according to the warehouse receiving configuration requirements.

# Parameter description:

# The first parameter is the bad file.

# 2nd parameters are log files

Start_loader ()

{

Badfile_name = $1;

Logfile_name = $2;

# Here 10000 refers to how many data records are committed at a time, and the size can be adjusted according to the actual situation.

Dbload-d $ dbconnt-c "$ ctl_file"-l "$ badfile_name"-N 10000;

}

Run ()

{

# Obtain the user name with the user ID 1001.

Get_username_bycode "1001 ";

# Obtain the user ID, user name, and user description in the t_test table and save the information to the t_test.dat file.

Get_all_username;

# Use awk to convert the last t_test.dat column to uppercase letters

'Cat t_test.dat | awk-F', '-v ofs =', ''{print $1, $2, toupper ($3)} '> t_test2.dat'

Ctl_file = "./t_test2.ctl"

# Generate warehouse receiving configuration

Make_loader "t_test2" "./t_test2.dat" "," "3 ";

# Execute warehouse receiving

Start_loader "./t_test2.bad" "./t_test2.log ";

}

# -------------------- Main program --------------------

Run;

 

4.3. Running results and analysis
Test @ dwserver:/home/test/SGY $ LS-l

Total 4

-Rwxr-XR-x 1 testtest 2683 05-04 14:37 test. Sh

Test @ dwserver:/home/test/SGY $./test. Sh -------- run test. Sh

Select user_name from t_test where user_code = 1001; ---- 49 lines of code output

User_name: [test] ---- 52 lines of code output

Select user_code, user_name, user_desc from t_test; ---- 59 line of code output

Dbload load utility INFORMIX-SQL version 10.00.uc5 --- dbload run output

Copyright IBM Corporation 1996,200 4 All Rights Reserved

Software serial number AAA # b000000

Table t_test2 had 2 row (s) loaded into it.

Test @ dwserver:/home/test/SGY $ LS-l

Total 16

-Rwxr-XR-x 1 testdba 2683 05-04 test. Sh

-RW-r -- 1 test DBA 0 05-04 14:38 t_test2.bad

-RW-r -- 1 test DBA 59 05-04 14:38 t_test2.ctl

-RW-r -- 1 test DBA 51 05-04 14:38 t_test2.dat

-RW-r -- 1 test DBA 53 05-04 14:38 t_test.dat

Test @ dwserver:/home/test/SGY $ head t_test2.ctl ---- warehouse receiving configuration file

File "./t_test2.dat" delimiter "," 3;

Insert into t_test2;

Test @ dwserver:/home/test/SGY $ head t_test.dat ---- export the t_test record

1001, test, this is a test

1002, guess, this is a test

Test @ dwserver:/home/test/SGY $ head t_test2.dat ---- records processed by 99 lines of awk

1001, test, this is a test

1002, guess, this is a test after the end of the operation, the t_test2 table has two more records, and the value of the user_desc field is in upper case.

Conclusion

This article describes how to implement the interaction between Informix databases using shell in Linux/Unix. This article first briefly introduces INFORMIX and shell, and then introduces the usage of dbaccess and dbload applications to give readers a basic understanding of the interaction between Shell and Informix. At the end of this article, we will use a complete example to demonstrate the various interactions between Shell and Informix, so that readers can better understand them. In this example, awk and SED will be used to format the data, the addition of these tools makes shell more powerful when processing many applications, so in various practical applications, shell will combine awk, sed and other tools to format the exported information in the database, or format the records in the file before the file is imported into the database.

Introduction

Zhang xiaodan, ibm cdl software engineer, has rich experience in automated testing, rqm application, Bi project testing, and Web application development.

Cheng Pingping, an ibm cdl software engineer, is mainly engaged in the development of optim query tuner products. He has some experience in Eclipse plug-in development.

Wang Ping, ibm cdl software engineer, is mainly engaged in functional testing of DB2 for Z/OS products, and has some experience in developing database applications and automated testing tools.

 

 

This article from the Linux community website (www.linuxidc.com) original link: http://www.linuxidc.com/Linux/2012-06/63345.htm