| SQL term | IBM i term |
|---|---|
| TABLE | PHYSICAL FILE |
| ROW | RECORD |
| COLUMN | FIELD |
| INDEX | KEYED LOGICAL FILE |
| VIEW | NON-KEYED LOGICAL FILE |
| SCHEMA | LIBRARY |
| LOG | JOURNAL |
| ISOLATION LEVEL | COMMITMENT CONTROLE LEVEL |
| PARTITION | MEMBER |
sprintf(hKey, "__db2_%s.%s.%s", uid, database, password);
CREATE TABLE DEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
PRIMARY KEY(DEPTNO))
INSERT INTO DEPT (DEPTNO, DEPTNAME)
VALUES ('B11', 'PURCHASING'),
('E41', 'DATABASE ADMINISTRATION') ;
CREATE PROCEDURE TESTMULTIRS --We do not use this input parameter. (IN i_cmacct CHARACTER(5)) RESULT SETS 2 LANGUAGE SQL BEGIN DECLARE csnum INTEGER; --Declare serial cursors as serial cursor consume less resources --and we do not need rollable cursor. DECLARE getDeptNo CHAR(50); --Be careful about estimated length here. DECLARE getDeptName CHAR(200); DECLARE c1 CURSOR WITH RETURN FOR s1; DECLARE c2 CURSOR WITH RETURN FOR s2; SET getDeptNo = 'SELECT DEPTNO FROM DEPT'; SET getDeptName = 'SELECT DEPTNAME FROM DEPT'; PREPARE s1 FROM getDeptNo; OPEN c1; PREPARE s2 FROM getDeptName; OPEN c2; END;
call testmultirs('jia');
SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), eur) FROM SYSIBM.SYSDUMMY1;Output EUR format:
| 2011-05-28 15:36:12.178455 | 28.05.2011 |
SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), usa) FROM SYSIBM.SYSDUMMY1;Output USA format:
| 2011-05-28 15:38:57.487165 | 05/28/2011 |
SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), jis) FROM SYSIBM.SYSDUMMY1;Output JIS format:
| 2011-05-28 15:40:00.455681 | 2011-05-28 |
SELECT CURRENT_TIMESTAMP, char(date(CURRENT_TIMESTAMP), iso) FROM SYSIBM.SYSDUMMY1;Output ISO format:
| 2011-05-28 15:41:12.159374 | 2011-05-28 |
SELECT CURRENT_TIMESTAMP, char(date (CURRENT_TIMESTAMP), local) FROM SYSIBM.SYSDUMMY1;Output LOCAL format:
| 2011-05-28 15:42:41.802835 | 05/28/11 |
REPLACE INTO. The other is using ON DUPLICATE KEY UPDATE *NOMAX. This wont be good answer as the number of job should be optimized to a number, which fits your applications requirement.WRKJOB JOB(QYPSJSVR/QYPSJSVR) can be used to do further investigate. For QSQSRVR job itself, we can use command WRKSBSD SBSD(QSYS/QSYSWRK) or command WRKJOB JOB(QSQSRVR) to do deeper investigation. For QZDASOINIT job, its deamon job is QZDASRVSD. We can use command WRKJOB JOB(QZDASRVSD) or WRKSBSD SBSD(QSYS/QSERVER) work with it further. Command CHGPJE can be used to change the configuration of prestart jobs. Also, don't forget that iNavigator is a convenience tool for us to investigate too: Work Management -> Server Jobs . The database server prestart jobs (QZDAINIT, QZDASOINIT, and QZDASSINIT) by default are shipped to run in subsystem QSERVER QZDAINIT) and QUSRWRK (QZDASOINIT and QZDASSINIT).
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author Yiyu Jia
*/
public class DummyUDF {
boolean debugtableOn = true;
public static String helloJia(String name)throws SQLException, Exception {
//Connection con = DriverManager.getConnection("jdbc:default:connection");
return "hello" + name;
}
}
CREATE FUNCTION YOURSCHEMA.DummyUDF ( NAME VARCHAR(500) ) RETURNS VARCHAR(1000) LANGUAGE JAVA SPECIFIC YOURSCHEMA.DummyUDF NOT DETERMINISTIC MODIFIES SQL DATA RETURNS NULL ON NULL INPUT EXTERNAL NAME 'DummyUDF.helloJia' PARAMETER STYLE JAVA ;
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] DummyUDF in *LIBL type *N not found. ...values(YOURSCHEMA/DummyUDF('Yiyu'));
Since my iNavigator is configured to use system naming. I use "/" to link the schema name and function name. However, this wont work!After research and trying, I found it is extremely simply to solve this problem. That is, using SQL naming instead of System naming to call UDF though iNavigator is set to use system naming already. Below code works,values(YOURSCHEMA.DummyUDF('Yiyu'));
db2_pconnect to db2_connect to see different output. You need to run this script many times to see that you can not ALWAYS get the right schema as your current schema. Most likely, you need to take 5 minutes to 10 minutes break during the testing and see the wrong result to come out. <?php
$sysStr = 'select CURRENT SCHEMA from SYSIBM.SYSDUMMY1 ';
$params = array(
'username' => 'accountNameOne',
'password' => 'accountPwdOne',
'dbname' => '*LOCAL',
'driver_options' => array('i5_lib' => 'libraryOne')
);
$connOne = db2_pconnect($params['dbname'], $params['username'],
$params['password'], $params['driver_options']);
$sysStmt = db2_prepare($connOne, $sysStr);
db2_execute($sysStmt);
$returnObj = db2_fetch_object($sysStmt);
var_dump($returnObj);
$params2 = array(
'username' => 'accountNameTwo',
'password' => 'accountPwdTwo',
'dbname' => '*LOCAL',
'driver_options' => array('i5_lib' => 'libraryTwo')
);
$connTwo = db2_pconnect($params2['dbname'], $params2['username'],
$params2['password'], $params2['driver_options']);
$sysStmt2 = db2_prepare($connTwo, $sysStr);
db2_execute($sysStmt2);
$returnObj2 = db2_fetch_object($sysStmt2);
var_dump($returnObj2);
package jia.blog.db2;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author Yiyu Jia
*/
public class Db2MetaData {
Connection conn = null;
DatabaseMetaData dmd = null;
public Db2MetaData() {
}
public void openConnection () {
try {
String url = "jdbc:as400://172.0.0.1"; //change ip address
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
conn = DriverManager.getConnection(url, "accountName", "password");
}
catch (Exception e) {
Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
}
}
public void closeConnection () {
if (conn != null) {
try {
conn.close();
System.out.println("Connection closed");
}
catch(SQLException e) {
Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
}
}
}
public void displayMaxConnection() {
try {
DatabaseMetaData metadata = conn.getMetaData();
int maxConnection = metadata.getMaxConnections();
System.out.println("Maximum Connection = " + maxConnection);
} catch (SQLException ex) {
Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void displayTypeInfo() {
try {
DatabaseMetaData metadata = conn.getMetaData();
ResultSet resultSet = metadata.getTypeInfo();
while (resultSet.next()) {
String typeName = resultSet.getString("TYPE_NAME");
System.out.println(typeName);
}
resultSet.close();
} catch (SQLException ex) {
Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
}
}
public static void main(String args[]) {
Db2MetaData db2Meta = new Db2MetaData();
db2Meta.openConnection();
db2Meta.displayMaxConnection();
db2Meta.displayTypeInfo();
db2Meta.closeConnection();
}
}
| DB2 i5 R6V1 | MySQL 5.5.8 |
|---|---|
CHAR CHAR() FOR BIT DATA GRAPHIC LONG VARGRAPHIC LONG VARCHAR FOR BIT DATA VARCHAR VARCHAR() FOR BIT DATA VARGRAPHIC DATE TIMESTAMP TIME DECIMAL DOUBLE FLOAT INTEGER NUMERIC REAL SMALLINT BIGINT ROWID DECFLOAT DATALINK BLOB CLOB DBCLOB | BIT BOOL TINYINT TINYINT UNSIGNED BIGINT BIGINT UNSIGNED LONG VARBINARY MEDIUMBLOB LONGBLOB BLOB TINYBLOB VARBINARY BINARY LONG VARCHAR MEDIUMTEXT LONGTEXT TEXT TINYTEXT CHAR NUMERIC DECIMAL INTEGER INTEGER UNSIGNED INT INT UNSIGNED MEDIUMINT MEDIUMINT UNSIGNED SMALLINT SMALLINT UNSIGNED FLOAT DOUBLE DOUBLE PRECISION REAL VARCHAR ENUM SET DATE TIME DATETIME TIMESTAMP |
SQLSetEnvAttr() via the SQL_ATTR_SERVER_MODE attribute. The other is using QWTCHGJB() Work Management API, via the "Server mode for Structured Query Language" key 1922 within the JOBC0200 format. CHGPJE SBSD(QSYS/QSYSWRK) PGM(QSYS/QSQSRVR)WRKJOB JOB(QYPSJSVR/QYPSJSVR) Work Management --> Subsystem --> Active Subsystems --> Qsyswrk STRDBMON OUTFILE(MYLIB/QSQMON1) JOB(*ALL/*ALL/QSQSRVR)WRKPTFGRP to list all installed PTF.QIBM_SRVRMODE_SBS to be "*SAME" or "". Here is the command to add this environment variable, ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('*SAME') LEVEL(*SYS). To remove environment variable, command RMVENVVAR is used. row_num() to implement this as below,select * from (select col1, row_number() over() as rownum from schemaname.tablename)end where rownum < 10 and rownum > 6 ;
call myStoredProcedure('update dummyTbl set columnA = 1 where columnB = ''Jia'' ')
Message: [SQL7008] dummyTbl in schemaA not valid for operation. Cause . . . . . : The reason code is 3. Reason codes are: 1 -- dummyTbl has no members. 2 -- dummyTbl has been saved with storage free. 3 -- dummyTbl not journaled, no authority to the journal, or the journal state is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.The interesting thing is that I get error free when I run the same SQL query in System i Navigator.
SET TRANSACTION ISOLATION LEVEL NO COMMIT
SET @jia = NULL ; SELECT id, sum( sales ) AS sales, count( * ) AS sig, @jia := IFNULL( @jia , 0 ) +1 AS rank FROM brandpromo GROUP BY id ORDER BY sales DESC LIMIT 100 ;I was only aware that the user defined variable is only connection session scope alive. I did not know that it is dangerous to use user defined variables together with GROUP BY and ORDER BY clause. So, DO NOT use user defined variable together with GROP BY and ORDER BY in MySQL server. At least, be careful when you want to use it in that way.