DB2 database, trigger, stored procedure, JAVA Database Link (JDBC)

 

Trigger

Create a trigger that includes the following parts:

Trigger name

Trigger trigger event: insert, delete, update

Activation time: before, after

Granularity: for each statement, for each row

Transition variable:

Old row: indicates the modified value before the event is triggered:

New row indicates the value modified after the event is triggered.

Old table indicates a read-only hypothetical table of all modified rows before the event is triggered.

New table indicates a hypothetical table in which all rows are modified after the event is triggered.

Trigger condition: starting from WHEN. It can contain one or more predicates, including transition variables and subqueries.

Trigger body: composed of one or more SQL statements

Example:

CREATE TRIGGER REORDER

After update of ON_HAND, MAX_STOCKED ON PARTS

Referencing new as N_ROW

For each row mode DB2SQL

WHEN (N_ROW.ON_HAND <0.10 * N_ROW.MAX_STOCKED

AND N_ROW.ORDER_PENDING = 'n ')

BEGIN ATOMIC

VALUES (ISSUE_SHIP_REQUEST (N_ROW.MAX_STOCKED-

N_ROW.ON_HAND,

N_ROW.PARTNO ));

Update parts set parts. ORDER_PENDING = 'y'

Where parts. PARTNO = N_ROW.PARTNO;

END

Stored Procedure

The Stored Procedure is mainly created through "Stored Procedure Builder,

(1) calling a stored procedure is divided into three parts:

1. Connection (establish a connection with the database)

Class. forName ("COM.ibm.db2.jdbc.net. DB2Driver"). newInstance ();

Connection con = DriverManager. getConnection (url, user, password );

2. Register output parameters

Cs. registerOutParameter (3, Types. INTEGER );

3. Call the stored procedure:

CallableStatement cs = con. prepareCall ("{call store_name (parameter, parameter, parameter )}");

(2) Call example:

Import java.net. URL;

Import java. SQL .*;

Class test2

{

Public static void main (String args [])

{

String url = "jdbc: db2: // wellhope/sample ";

String user = "db2admin ";

String password = "db2admin ";

Try

{

Class. forName ("COM.ibm.db2.jdbc.net. DB2Driver"). newInstance ();

// Establish a connection with the database

Connection con = DriverManager. getConnection (url, user, password );

CheckForWarning (con. getWarnings ());

DatabaseMetaData dma = con. getMetaData ();

String str = "This is a string ";

// Int hashcode = str. hashCode ();

// System. out. println ("Hashcode" + hashcode );

// Create a Statement object for executing SQL statements

Statement stmt = con. createStatement ();

// Create a CallableStatement object for executing the Stored Procedure

CallableStatement cs = con. prepareCall ("{call PRO_YHDL1 (?,?,?)} ");

// Register output parameters

Cs. registerOutParameter (3, Types. INTEGER );

Int result = 0;

Cs. setString (1, "123 ");

Cs. setString (2, "123 ");

Cs.exe cute ();

Result = cs. getInt (3 );

DispResultSet (result );

Cs. close ();

Con. close ();

}

Catch (SQLException ex)

{

System. out. println ("*** SQLException caught ***");

While (ex! = Null)

{

System. out. println ("SQLState:" + ex. getSQLState ());

System. out. println ("Message:" + ex. getMessage ());

System. out. println ("Vendor:" + ex. getErrorCode ());

Ex = ex. getNextException ();

System. out. println ("");

}

}

Catch (java. lang. Exception ex)

{

Ex. printStackTrace ();

}

}

(3) Example of stored procedure:

Pro_yhdl1 is a stored procedure. Its function is to retrieve PWD from the database table YHDL:

Import java. SQL .*;

Public class Pro_yhdl1

{

Public static void pro_yhdl1 (String m_id,

String m_pwd,

Int [] result) throws SQLException, Exception

{

// Get connection to the database

Connection con = DriverManager. getConnection ("jdbc: default: connection ");

PreparedStatement stmt = null;

ResultSet rs = null;

String SQL;

String m_password = "";

SQL = "SELECT"

+ "DB2ADMIN. YHDL. PWD"

+ "FROM"

+ "DB2ADMIN. YHDL"

+ "WHERE"

+ "("

+ "("

+ "DB2ADMIN. YHDL. ID = '" + m_id.trim () + "'"

+ ")"

+ ")";

Stmt = con. prepareStatement (SQL );

Rs = stmt.exe cuteQuery ();

// Access query results

While (rs. next ())

{

M_password = rs. getString (1 );

M_password = m_password.trim ();

If (rs. wasNull ())

System. out. print ("NULL ");

Else

System. out. print (m_password );

}

If (m_password.equals (m_pwd.trim ()))

{

Result [0] = 1;

}

Else

{

Result [0] = 0;

}

// Close open resources

If (rs! = Null) rs. close ();

If (stmt! = Null) stmt. close ();

If (con! = Null) con. close ();

// Set return parameter

// Result [0] = result [0];

}

}

JAVA Database Link (JDBC)

DB2 Java supports JDBC, a supplier-centric dynamic SQL interface, which provides data access to applications through standard Java methods. JDBC is similar to DB2 CLI in that you do not have to pre-compile or compile JDBC programs. Applications compiled using JDBC only use dynamic SQL.

JDBC programming steps:

1. Establish a connection with the database:

Class. forName ("Com.ibm.db2.jdbc.net. DB2Driver ");

Connection con = DriverManager. getConnection (url );

2. Create a Statement object:

Statement stmt = con. createStatement ();

3. Execute the query statement:

ResultSet rs1_stmt.exe cQuery ("SQL statement ");

4. Get and set options:

ResultSetMetaData rsmd = rs. getMetaData ();

Int numCols = rsmd. getColumnCount () to obtain the total number of columns in the result set;

Rsmd. getColumnLabel (I) to obtain the record value;

SetMaxRows: set the maximum number of rows that a result set can accommodate.

SetQueryTimeout: Set the waiting time for a statement to be executed.

SetEscapeProcessing: notifies the driver of how to handle escape characters.

5. Disable Statement

Stmt. clost ();

Call layer interface (CLI)

CLI is not a new query language, but a simple interface for applications to use SQL statements to submit transactions for database query and modification, you still need to write it in SQL, including calling CLI functions.

The call layer interface (CLI) is one of the two mechanisms provided by DB2 to process dynamic SQL statements. That is, when an application is running for the first time, dynamic SQL statements are submitted to the database system, CLI depends on a group of function calls and can be embedded into the subject.