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

Source: Internet
Author: User



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




Referencing new as N_ROW

For each row mode DB2SQL







Update parts set parts. ORDER_PENDING = 'y'

Where parts. PARTNO = N_ROW.PARTNO;


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 ";



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 = "";



+ "FROM"



+ "("

+ "("

+ "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 ");


System. out. print (m_password );


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


Result [0] = 1;




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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.