DB2 database, transaction control language, system control language, function, embedded SQL (SQLJ)

 

Transaction Control Language

1. transaction Commit command: Commit;

2. Transaction Rollback command: Rollback;

System Control Language

1. Cancel automatic submission:

Update command options using c off;

2. Connect to the database:

Connect to Database Name user using Password

3. Disconnect the database:

Connect reset

Disconnect Database Name

4. list all tables in the database:

List tables for all

5. list all tables in the database with the schema name schema_name:

List tables for schema schema_name

6. view the table structure

Describe table mode name. table Name

Describe select * from Schema name. Table Name

7. view the table Index

Describe indexes for table mode name. table Name

Function

(1) column Functions

The column function computes a group of values in a column to obtain a single result value.

1. AVG

Returns the sum of the values in a group divided by the number of values in the group.

2. COUNT (*)

Returns the number of rows of non-empty column values.

3. MAX

Returns the maximum value of a group of values.

4. MIN

Returns the minimum value of a set of values.

5. MOD

Remainder

(2) scalar functions

A scalar function performs an operation on the value to return another value. The following are some examples of scalar functions provided by the DB2 Universal Database.

1. ABS

Absolute Value of the returned number

2. HEX

Hexadecimal representation of the returned value

3. LENGTH

Returns the number of bytes in the independent variable (for a string, double-byte characters are returned .)

4. YEAR

Extract the year of the datetime Value

5. NULLIF (a, B)

If a = B, the value is null; otherwise, the value is.

6. COALESCE (a, B, c)

: Returns the value of the first parameter with a non-null value.

7. UCASE (str)

Converts lowercase to uppercase or lowercase.

8. ICASE (str)

Convert uppercase to lowercase

9. LOCAT (str1, str2, n)

Returns the position where str2 first appears in str1 from the nth character.

10. SUBSTR (str, m, n)

Returns n strings in str starting from the MTH character.

Embedded SQL (SQLJ)

To embed an SQL statement into an application, you must precompile the application and associate it with the database by following these steps:

1. Create source files to include programs with embedded SQL statements

Format: # SQL {SQL statement }.

2. Connect to the database and pre-compile each source file.

Syntax: SQLJ source file name

Example:

Import java. SQL .*;

Import sqlj. runtime .*;

Import sqlj. runtime. ref .*;

# SQL iterator App_Cursor1 (String empno, String firstnme );

# SQL iterator App_Cursor2 (String );

Class App

{

Static

{

Try

{

Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver"). newInstance ();

}

Catch (Exception e)

{

E. printStackTrace ();

}

}

Public static void main (String argv [])

{

Try

{

App_Cursor1 cursor1;

App_Cursor1 cursor2;

String str1 = null;

String str2 = null;

Int count1;

Connection con = null;

String url = "jdbc: odbc: tese2 ";

DefaultContext ctx = DefaultContext. getdefacontext context ();

If (ctx = null ){

Try {

If (argv. length = 0 ){

String userid = "tdl ";

String passwd = "user ";

Con = DriverManager. getConnection (url, userid, passwd );

}

Else if (argv. length = 2 ){

// Connect with default id/password

Con = DriverManager. getConnection (url );

}

Else {

System. out. println ("Usage: java App [username password]");

System. exit (0 );

}

Con. setAutoCommit (false );

Ctx = new DefaultContext (con );

}

Catch (SQLException e ){

System. out. println ("Error: cocould not get a default context ");

System. err. println (e );

System. exit (1 );

}

DefaultContext. setdefacontext context (ctx );

}

# SQL cursor1 = {SELECT empno, firstnme from db2admin. employee };

System. out. println ("Received results :");

While (cursor1.next ()){

Str1 = cursor1.empno ();

Str2 = cursor1.firstnme ();

System. out. print ("empno =" + str1 );

System. out. print ("firstname =" + str2 );

System. out. print ("");

}

Cursor1.close ();

# SQL cursor2 = {SELECT firstnme from db2admin. employee where empno =: str1 };

System. out. println ("Received results :");

While (true ){

# SQL {FETCH: cursor2 INTO: str2 };

If (cursor2.endFetch () break;

System. out. print ("empno =" + str1 );

System. out. print ("firstname =" + str2 );

System. out. print ("");

}

Cursor2.close ();

// Rollback the update

System. out. println ("Rollback the update ...");

# SQL {ROLLBACK work };

System. out. println ("Rollback done .");

}

Catch (Exception e)

{

E. printStackTrace ();

}

}

}

Note: The program uses the JDBCODBC bridge to access the database. The ODBC data source must be configured.