4, GRANT (empowerment)
u For [public / user / group], [all / select / INSERT / UPDATE / DELETE] permissions on the table:
Grant [all / select / insert / update / delete] ON [.] Table name to [public / user /
group];
u For [public / user / group] [bind / execute /] permission on the package:
Grant [bind / execute /] on package [.] [PUBLIC / User / Group];
u For [Public / User / Group], [Control] permissions on the index:
Grant Control on Index [.] Index To [PUBLIC / User / Group]
5, Revoke (recovery)
u From public / user / group] Recycled [All / SELECT / INSERT / UPDATE / DELETE] permissions on the table:
Revoke [All / Select / Insert / Update / Delete] ON [Name] from [PUBLIC / User / Group];
u From Public / User / Group] Recycled [BIND / EXECUTE /] permission on the package:
Revoke [Bind / Execute /] On Package [.] [PUBLIC / User / Group];
Three transaction control language
1. Transaction submit command: commit;
2. Transaction rollback command: rollback;
Four system control language
1. Cancel automatic submission:
Update Command Options Using C OFF;
2. Connect to the database:
Connect to database name User user Using password
3. Disconnect the database connection:
Connect Reset
Disconnect database name
4. List all the tables in the database:
List table for all
5. List the schema named Schema_Name in the database:
List table for schema schema_name
6. View the table structure
Describe Table mode name. Name
Describe SELECT * FROM mode name. Table name
7. View the index of the table
Describe Indexes for Table Mode Name. Table Name
Five function
(1) Column function
The column function calculates a set of values in the column to obtain a single result value.
1. AVG
Returns the value of a group removed in this group
2. Count (*)
Returns the number of non-air column values.
3. Max
Returns the maximum value in a set of values
4. MIN
Returns the minimum value in a set of values
5. MOD
Surplus
(2) Scalar function
The scalar function performs a value to return another value. The following is some examples of scalar functions provided by the DB2 universal database.
1. ABS
The absolute value of the number of returns
2. HEX
Hexadecimal representation of the return value
3. Length
Returns the number of bytes in the argument (returns the number of double-word characters for the graphic string.)
4. Year
Extract the year part of the date time
5. Nullif (a, b)
If A = B is empty, the value is a
6. Coalesce (a, b, c)
: Returns the value of the first parameter with non-null value
7. Ucase (STR)
Small write characters convert to uppercase characters
8. Icase (STR)
Upper write characters convert to lowercase characters
9. Locat (STR1, STR2, N)
Return from the nth character, the position of STR2 in STR1 first appears
10. SUBSTR (STR, M, N)
Return from the mth character, N string in the STR
Six embedded SQL (SQLJ)
When embedding the SQL statement into the application, you must prepare the application and follow the database as follows:
1. Create a source file to include a program with embedded SQL statement
Format: # SQL {SQL statement}.
2. Connect the database and prepare 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.getDefaultContext ();
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 ("/ Nusage: Java App [Username Password] / N");
System.exit (0);
}
Con.SetAutocommit (False);
CTX = New DefaultContext (con);
}
Catch (SQLException E) {
System.out.println ("Error: Could Not Get a Default Context");
System.err.Println (e);
System.exit (1);
}
DefaultContext.SetDefaultContext (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 ("/ n");
}
Cursor1.close ();
#SQL CURSOR2 = {SELECT Firstnme from db2admin.employee where empno =: str1};
System.out.Println ("Received Results:"); while (true) {
#SQL {FETCH: CURSOR2 INTO: STR2};
Cursor2.endFetch ()) Break;
System.out.print ("Empno =" STR1);
System.out.print ("firstname =" str2);
System.out.print ("/ n");
}
Cursor2.close ();
// rollback the Update
System.out.println ("/ n / nrollback the update ...");
#sql {rollback work};
System.out.Println ("Rollback Done.");
}
Catch (Exception E)
{
E.PrintStackTrace ();
}
}
}
Note: This program uses a JDBCODBC bridge to access the database, and you must configure an ODBC data source.
Seven trigger
Building a trigger should include the following sections:
Trigger name
Trigger Trigger Event: Insert, Delete, Update
Activation time: Before, after
Particle size: for Each Statement, for Each Row
Transition variable:
Old Row: Indicates the value that is modified before the event is triggered:
New row indicates the value modified after the event is triggered.
Old Table means a read-only false watch that is all modified before triggering events.
New Table means a virtual table that is all modified after triggering events
Trigger condition: Start by when, one or more predicates can be included, can include transition variables and subqueries
Trigger: consists 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
Eight storage procedure
The stored procedure is mainly based mainly through "Stored Procedure Builder".
(1) Division of calling processes in 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 (parameters, parameters, parameters)});
(2) Calling 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 to perform SQL statements
Statement Stmt = con.createstatement ();
// Create a CallableStateMent object to perform a stored procedure
Callablestate cs = con.preparecall ("{Call Pro_YHDL1 (?,?,?)}");
// Register output parameters
CS.RegisterOutparameter (3, Types.integer);
Int results = 0;
CS.STSTRING (1, "123");
Cs.setstring (2, "123");
cs.execute ();
Result = cs.getint (3);
DispResultSet (Result);
Cs.close ();
C. close ();
}
Catch (SQLException EX)
{
System.out.println ("/ n * * * sqlexception caught * * * / n");
While (ex! = null)
{
System.out.println ("SqlState:" ex.getsqlstate ());
System.out.println ("Message:" ex.getMessage ());
System.out.println ("vendor:" ex.GetersRrorcode ());
EX = ex.getnextexception ();
System.out.println ("");
}
}
Catch (java.lang.exception ex)
{
EX.PrintStackTrace ();
}
}
(3) Example of stored processes:
Pro_YHDL1 is a stored procedure, its function is to remove the 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.executeQuery ();
// 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))))
{
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];
}
}
Nine Java Database Links (JDBC)
The Java support of DB2 includes JDBC, a vendor-centric dynamic SQL interface, which provides data access to the application through standard Java methods. JDBC is similar to DB2 CLI that you don't have to prepose or associate JDBC programs. Applications written using JDBC use only dynamic SQL.
JDBC programming steps:
1 Establish a connection to 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 RS = Stmt.execQuery ("SQL Statement");
4. Get and set options:
ResultSetmetaData RSMD = rs.getMetadata ();
INT Numcols = rsmd.getColumnCount () Get the number of results set;
RSMD.GetColumnLabel (i)) Get recorded values;
SetMaxRows: Set the maximum number of lines accommodated by the result.
SetQueryTimeout: Sets a statement to perform a wait time.
SISSSCAPEPROCESSING: Notify the driver how to deal with the escape character. 5. Close Statement
Stmt.clost ();
Ten call layer interface (CLI)
The CLI is not a new query language. It is just a simple interface that the application can use the SQL statement to submit transaction processing. Query and modify the database, still use SQL language, including the CLI function call.
The calling layer interface (CLI) is one of the two mechanisms of the processing dynamic SQL statement provided by DB2, that is, the dynamic SQL statement is submitted to the database system when the application is running for the first time, and the CLI relies on a set of function calls, which can be embedded in the main language. .
转载请注明原文地址:https://www.9cbs.com/read-90256.html
