errori sqlerror:
DSPMSGD RANGE(SQL0326) MSGF(QSQLMSG)
0 Successful
-007 The specified 'character' is not a valid character in SQL statements. -010 THE string constant beginning with string is not terminated properly. -029 INTO Clause required. -060 INVALID type SPECIFICATION : spec -084 Unacceptable SQL statement. -101 The statement is too long or too complex. -102 String constant is too long. -104 Illegal symbol encountered in the SQL statement. -117 The number of values in the INSERT does not match the number of columns. -122 Column or Expression in the Select List is not valid -180 Bad data in Date/Time/Timestamp. -181 Bad data in Date/Time/Timestamp. -188 The host variable in a DESCRIBE statement is not a valid string representation of a name. -199 Illegal use of the specified keyword. -204 Object not defined to DB2. -205 Column name not in table. -206 Column does not exist in any table of the SELECT. -208 THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN column-name IS NOT PART OF THE RESULT TABLE -216 Not the same number of expressions on both sides of the comparison in a SELECT. -224 FETCH cannot make an INSENSITIVE cursor SENSITIVE. -229 The locale specified in a SET LOCALE statement was not found. -302 THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE -303 A VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE THE DATA TYPES ARE NOT COMPARABLE -305 Null indicator needed. -311 Varchar, insert or update. -LEN field with the right data length not set. -401 The data types of the operands of an operation are not compatible. -404 The Sql Statement specified contains a String that is too long. -407 AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES -408 A value is not compatible with the data type of its assignment target. Target name is "<name>". (***OBS: Para alguns casos o CAST resolve ) -418 Use of parameter marker not valid. -420 The value of a string argument was not acceptable to the 'function-name' function -438 Application raised error with diagnostic text: text -440 Routine &1 in &2 not found with specified parameters.A function or procedure with the specified name and compatible arguments was not found.[1] -482 The procedure returned no locators. -501 Cursor not open on FETCH. -502 Opening cursor that is already open. -503 Updating column needs to be specified. -504 Cursor name not declared. -530 Referential integrity preventing the INSERT/UPDATE -532 Referential integrity (DELETE RESTRICT rule) preventing the DELETE. -536 Referential integrity (DELETE RESTRICT rule) preventing the DELETE. -545 Check constraint preventing the INSERT/UPDATE. -551 Authorization failure. -554 An authorization ID or a role cannot GRANT a privilege to itself. -601 You tried to create an object that already exists -602 Too many columns specified in a create index. -603 a unique index cannot be created because the table contains rows which are duplicates with respect to the values of the identified columns and periods -604 a data type definition specifies an invalid length, precision, or scale attribute -607 operation or option operation is not defined for this object -610 warning: a create/alter on object has placed object in pending -611 only lockmax 0 can be specified when the lock size of the tablespace is tablespace or table -612 identifier is a duplicate name -613 the primary key or a hash key or a unique constraint is too long or has too many columns and periods -614 the index cannot be created or altered, or the length of a column cannot be changed because the sum of the internal lengths of the columns for the index is greater than the allowable maximum -615 operation-type is not allowed on a package in use -616 obj-type1 obj-name1 cannot be dropped because it is referenced by obj-type2 obj-name2 -617 a type 1 index is not valid for table -618 operation operation is not allowed on system databases -619 operation disallowed because the database is not stopped -620 keyword keyword in stmt-type statement is not permitted for a space-type space in the database-type database -621 duplicate dbid dbid was detected and previously assigned to database-name -622 for mixed data is invalid because the mixed data install option is no -623 cluster is not valid for table-name -624 table table-name already has a primary key or unique constraint with specified columns and periods -625 table table-name does not have an index to enforce the uniqueness of the primary or unique key -625 warning: the definition of table has been changed to incomplete -626 the alter statement is not executable because the page set is not stopped -627 the alter statement is invalid because the table space or index has user-managed data sets -628 the clauses are mutually exclusive -629 set null cannot be specified because foreign key name cannot contain null values -630 error: the WHERE NOT NULL specification is invalid for type 1 indexes -631 foreign key name is too long or has too many columns -632 the table cannot be defined as a dependent of table-name because of DELETE rule restrictions -633 the DELETE rule must be DELETE-rule -634 the DELETE rule must not be cascade -635 the DELETE rules cannot be different or cannot be set null -636 ranges specified for partition part-num are not valid -637 duplicate keyword-name keyword or clause -638 table table-name cannot be created because column definition is missing -639 a nullable column of a foreign key with a DELETE rule of set null cannot be a column of the key of a partitioned index -640 locksize row cannot be specified because table in this tablespace has type 1 index -642 too many columns in unique constraints -643 a check constraint or the value of an expression for a column of an index exceeds the maximum allowable length key expression -644 invalid value specified for keyword or clause keyword-or-clause in statement stmt-type -645 WHERE NOT NULL is ignored because the index key cannot contain null values -646 table table-name cannot be created in specified table space table-space-name because it already contains a table -647 bufferpool bp-name for implicit or explicit tablespace or indexspace name has not been activated -650 the alter statement cannot be executed, reason reason-code copyright 2014 TheAmericanProgrammer.com Unauthorized copying prohibited -651 table description exceeds maximum size of object descriptor. -652 violation of installation defined edit or validation procedure proc-name -653 table table-name in partitioned table space tspace-name is not available because its partitioned index has not been created -655 the create or alter stogroup is invalid because the storage group would have both specific and non-specific volume ids -658 a object-type cannot be dropped using the statement statement -660 index index-name cannot be created or altered on partitioned table space tspace-name because key limits are not specified -661 object-type object-name cannot be created on partitioned table space tspace-name because the number of partition specifications is not equal to the number of partitions of the table space -662 a partitioned index cannot be created on a table space, or a table space cannot be index-controlled. table space tspace-name, reason reason-code -663 the number of key limit values is either zero, or greater than the number of columns in the key of index index-name -664 the internal length of the limit-key fields for the partitioned index exceeds the length imposed by the index manager -665 the partition clause of an alter statement is omitted or invalid -666 stmt-verb object cannot be executed because function is in progress -667 the clustering index for a partitioned table space cannot be explicitly dropped -668 the column cannot be added to the table because the table has an edit procedure defined with row attribute sensitivity -669 the object cannot be explicitly dropped. reason reason-code -670 the record length of the table exceeds the page size limit -671 the bufferpool attribute of the table space cannot be altered as specified because it would change the page size of the table space -672 operation drop not allowed on table table_name -676 the physical characteristics of the index are incompatible with respect to the specified statement. the statement has failed. reason reason-code -677 insufficient virtual storage for bufferpool expansion -678 the constant constant specified for the index limit key must conform to the data type data-type of the corresponding column column-name -679 the object name cannot be created because a drop is pending on the object -680 too many columns specified for a table, view or table function -681 column column-name in violation of installation defined field procedure. rt: return-code, rs: reason-code, msg: message-token -682 field procedure procedure-name could not be loaded -683 the specification for column, distinct type, function, or procedure data-item contains incompatible clauses -684 the length of constant list beginning string is too long -685 invalid field type, column-name -686 column defined with a field procedure can not compare with another column with different field procedure -687 field types incomparable -688 incorrect data returned from field procedure, column-name, msgno -689 too many columns defined for a dependent table -690 the statement is rejected by data definition control support. reason reason-code -691 the required registration table table-name does not exist -692 the required unique index index-name for ddl registration table table-name does not exist -693 the column column-name in ddl registration table or index name is not defined properly -694 the schema statement cannot be executed because a drop is pending on the ddl registration table table-name -694 the ddl statement cannot be executed because a drop is pending on the ddl registration table -695 invalid value seclabel specified for security label column of table table-name -696 the definition of trigger trigger-name includes an invalid use of correlation name or transition table name name. reason code=reason-code -697 old or new correlation names are not allowed in a trigger defined with the for each statement clause. old_table or new_table names are not allowed in a trigger with the before clause. -747 The table is not available. -803 Duplicate key on insert or update. -805 DBRM or package not found in plan. -811 More than one row retrieved in SELECT INTO. -818 Plan and program: timestamp mismatch. -904 Unavailable resource. Someone else is locking your data. -911 Deadlock or timeout. Rollback has been done. -913 Deadlock or timeout. No rollback. -922 Authorization needed. -924 DB2 Connection internal error. -927 The language interface was called but no connection had been made. -998 Error occurred during transaction or heuristic processing. -30090 Remote operation invalid for application execution environment.
+100 Row not found or end of cursor +222 Trying to fetch a row within a DELETE statement +223 Trying to fetch a row within an UPDATE statement +231 FETCH after a BEFORE or AFTER but not on a valid row +304 Value cannot be assigned to this host variable because it is out of range +802 The null indicator was set to -2, an arithmetic statement didn't work
php ibm_db2 extension:
<?php
$conn = db2_connect($database, $user, $password);
$sql = "SELECT * FROM lib.file ";
if ($conn) {
$stmt = db2_exec($conn, $sql, ['cursor' => DB2_SCROLLABLE] );
while ($row = db2_fetch_array($stmt)) {
echo "$row[0]\n";
}
} else {
die('connection failed');
}
installazione ODBC:
il driver di odbc di ibm contenuto nel pacchetto client access
sudo apt-get install unixodbc alien alien -d -c -k -v iSeriesAccess-7.1.0-1.0.x86_64.rpm sudo dpkg -i iseriesaccess_7.1.0-1.0_amd64.deb sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwbcore.so /usr/lib/libcwbcore.so sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so /usr/lib/libcwbodbc.so sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so /usr/lib/libcwbodbcs.so sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwbrc.so /usr/lib/libcwbrc.so
aggiungere nel file /etc/odbc.ini DSN (data source name) che si possa chiamare per nome dalle applicazioni
[ODBC_NAME] Description = iSeries Access ODBC Driver Driver = iSeries Access ODBC Driver System = 192.168.1.1 UserID = $USR Password = $PASS Naming = 0 DefaultLibraries = QGPL QSYL MY_OBJ Database = ConnectionType = 0 CommitMode = 2 ExtendedDynamic = 1 DefaultPkgLibrary = QGPL DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512 AllowDataCompression = 1 LibraryView = 0 AllowUnsupportedChar = 1 AllowProcCalls = 1 ForceTranslation = 1 Trace = 0
logging:
[ODBC] Trace=yes TraceFile=/tmp/unixodbc.log
verifica delle connessione:
isql ODBC_NAME
con DNS:
<?php
$dsn = 'ODBC_NAME';
try {
$conn = new PDO("odbc:".$dsn, $username='', $password='',
[ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
echo 'connected';
} catch (Exception $e) {
echo $e->getMessage();
}
$sql = 'select * from MY_LIB.MYFILE fetch first 5 rows only ';
$stmt = $conn->prepare($sql);
$vals = $stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $a){
echo '<pre>',var_dump( array_keys($a) ),'</pre>';
die(__FUNCTION__.__FILE__);
}
senza DNS:
$pdo_dsn = "odbc:DRIVER={iSeries Access ODBC DRIVER};SYSTEM===*";
$pdo_dbh = new PDO(
$pdo_dsn,
$pdo_username,
$pdo_password,
array(
PDO::ATTR_PERSISTENT => FALSE,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
ODBC functions:
<?php
$db_host = "server.mynetwork";
$db_user = "dbuser";
$db_pass = "dbpass";
$c = odbc_connect($db_host, $db_user, $db_pass, "SQL_CUR_USE_ODBC");
odbc_setoption($c, 1, SQL_ATTR_COMMIT, SQL_TXN_NO_COMMIT)
or die('Failed setoption: ' . odbc_error() . ":" . odbc_errormsg());
odbc_setoption($c, 1, SQL_ATTR_DBC_DEFAULT_LIB, $this->dbname)
or die('Failed select: ' . odbc_error() . ":" . odbc_errormsg());
DB2 è case insensitive: è convenzione usare sempre nomi degli oggetti in uppercase.
squirrelSQL funziona con il driver odbc
JT400 com.ibm.as400.access.AS400JDBCDriver
il jar deve trovarsi nel classpath di esecuzione del programma, quindi modificare lo script di avvio o copiare in squirrel/lib
dal pannelo drivers, aggiungi drivers, caricare il jar driver da external class path, list drivers.
Example URL:
jdbc:as400://$IPAddress
Class Name:
com.ibm.as400.access.AS400JDBCDriver
| Desired action | MySQL statement | DB2 statement |
|---|---|---|
| To return a selected number of rows from a table | SQL -> LIMIT | SQL -> FETCH FIRST n ROWS |
| To determine last generated ID of a table after an insert statement | PHP -> mysql_insert_id | SQL -> SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1; |
| To show tables | SQL -> SHOW TABLES FROM table_name | SQL -> SELECT TABLE_NAME FROM sysibm.SYSTABLES WHERE TABLE_NAME=table_name |
| To show indexes | SQL -> SHOW INDEX FROM table_name | SQL -> SELECT INDEX_NAME FROM sysibm.SYSINDEXES WHERE TABLE_NAME=table_name |
| To determine number of rows affected by an SQL statement | PHP -> mysql_num_rows() for SELECT, mysql_affected_rows() for INSERT,UPDATE,DELETE | PHP -> db2_num_rows() (if it uses scrollable cursor) |
| To set a column to automatically generate and increment its values | auto_increment | GENERATED BY DEFAULT AS IDENTITY |
| To escape/quote a character | slash ('\') | ( ' ) es. "Smith''s" |
| To insert a key into a table where a primary key already exists | SQL-> IGNORE drops the insert statement |
| MySQL | DB2 |
|---|---|
| Show databases | SELECT TABLE_SCHEM FROM SYSIBM.SQLSCHEMAS |
| Show tables | SELECT TABLE_NAME FROM SYSTABLES WHERE TABLE_SCHEMA = schema_name |
| Show index from table_name | SELECT INDEX_NAME FROM SYSINDEXES WHERE TABLE_NAME = table_name AND INDEX_SCHEMA = schema_name |
see also: IBM DB2 Migration Toolkit (MTK)
AND fieldname IS NOT NULL
la query incontra almeno un campo con valore NULL. ODBC linux, (o RPG) non gestiscono correttamente e danno errore
Message: SQLSTATE[24000]: Invalid cursor state: 30022 [IBM][System i Access ODBC Driver]Invalid cursor state. (SQLFetchScroll[30022] at /build/buildd/php5-5.4.9/ext/pdo_odbc/odbc_stmt.c:537)
CREATE TABLE student ( sid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) ,sname VARCHAR(30) ,PRIMARY KEY (sid) );
SELECT * FROM MY_LIB.MYFILE fetch FIRST 5 ROWS ONLY
To get ranges, you'd have to use ROW_NUMBER() (since v5r4) and use that within the WHERE clause:
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY BCOART ) RID_, ART.BCOART, ART.BDEART, ART.BALIVA FROM MI_DAT.MATP200F ART ORDER BY BCOART WHERE ART.BALIVA > 20 ) AS PAG_TBL_ WHERE PAG_TBL_.RID_ BETWEEN 10 AND 30 -- limit equivalent ORDER BY BALIVA -- custom ordering
scrollable cursor(in RPG e PHP):
// verificare prepare
$sql = "SELECT * FROM $lib.FILE00F ORDER BY id";
$stmt = db2_prepare($conn, $sql);
$result = db2_exec($conn, $stmt /*$sql*/, array(
'cursor' => DB2_SCROLLABLE,
'rowcount' => DB2_ROWCOUNT_PREFETCH_ON
));
$limit = 30;
for ($i=100; $i<=$limit; $i++){
$row = db2_fetch_assoc($result, $i);
}
-- cancellare dalla memoria la SP corrente, via squirrel o strsql DROP PROCEDURE lib_obj.SP_MYPROC;
-- ricompilare: -- in alternativa comando RS RUNSQLSTM SRCFILE(LIB_SRC/SRCSQL) SRCMBR(SP_MYPROC) DBGVIEW(*SOURCE) -- controlla compilazione WRKSPLF SELECT(*CURRENT *ALL *ALL *ALL *ALL SP_*) -- le procedure SQL EXTERNAL vanno anche compilato il reletivo sorgente RPGLE -- testa richimando con SQL call lib_obj.SP_MYPROC();
STRDBG SP_NAME F6 su main procedure strsql call PRJ_LIB/SP_NAME
Search File Data
STRQRY
CCSID deve essere settato a 280(italiano) systemwide, altrimenti se 65585 sballa la codifica caratteri
WRKSYSVAL SYSVAL(QCCSID)
poterbbe essere necessario riavviare il servizio
può essere utile il comando conversione caratteri
iconv -l iconv -from-code=ISO-8859-1 –to-code=UTF-8 ISO-8859-1.html > UTF-8.html
utile a visualizzare velocemente le tabelle DB senza eseguire SQL
RQ: mostra tutti i dati di un file 18: lancia STRDFU sul file, permette di modificare i record velocemente
RUNQRY QRY(*NONE) QRYFILE( LIB_DAT/FILE01L)
DSPFFD: descrizione colonne tabella è possibile recuperare informazioni sulle colonne di una tabella con il comando
DSPFFD FILE(PRJ_LIB/FILE00F)
// output su file
DSPFFD FILE(PRJ_LIB/FILE00F) OUTPUT(*OUTFILE) OUTFILE(QTEMP/DFF_MATPF)
// CPYTOIMPF FROMFILE(qtemp/dff_matpf) TOSTMF('/home/username/matpf.txt') MBROPT(*ADD) RCDDLM(*CRLF)
che funziona anche su AGCDATV2, o dai sorgenti dei files custom che si trovano nella cartella del progetto
DSPDBR Display DataBase Relations: info sulle chiavi/indici relazionali/indici/constraints di una tabella
DSPDBR FILE(PRJ_LIB/FILE1) MBR(MEMBER1)
esempio di sessione:
// lettura del formato del file DSPDBR FILE(PRJ_LIB/FILE00F) // trovare la voce "File dipendenti dal file specificato" // che elenca i file logici definiti per il file fisico // di un file logico, quindi x leggere il dettaglio: DSPFD FILE(PRJ_LIB/FILE01L) // alla voce "Formato record" si trovano gli indici(label "Campo chiave") // attenzione che il file logico non abbia definito condizioni di filtro flag=A // in quel caso SQL esclude l'indice
errore in DSPMSG QSYSOPR:
Record non aggiunto. Il membro FILE100F completo.
succede se una procedura elimina e ripopola un file periodicamente, occorre settare con il flag "Riutilizzo record cancellati" YES:
CHGPF PRJ_LIB.FILE100F +F4 +F10
Logical Files combine the features of both Views (column selection and table joining) and Indexes (row ordering). They usually function as an Index but do show up as a View in Navigator. As a side note a Physical File (not table) can also have an Index.
SQL Tables, Views and Indexes are implemented in DB2 for iSeries using Physical (PF) and Logical Files (LF). The primary difference is when the database checks data integrity. It's checked on write for Tables and checked on read for Files. You can put trash data into a File but not into a Table.
There are actually many tiny differences between SQL created indexes/views and logical files created via DDS (that's the way of writing source files for your logical files (LF) and compiling them to LF-Objects).
two types of 'Logical Files' - keyed and unkeyed:
Logical files allow a user to access data in a format that is different from the way it is stored in one or more physical files. The logical file contains no data records. It contains the corresponding record number of the data record in the physical file. The logical file will contain the index to the physical file. Logical files provide the path to the physical file. There are four types of logical files. The first is the simple logical file. This maps data from a single physical file to another logical record definition. The second is the multiple-format logical file. This logical file allows access to several physical files. The third type of logical file is the join logical file. The join logical file defines a single record definition that is built from two or more physical files, tables, logical files, or views. The total number of physical files and tables cannot exceed 32. The forth type of logical file is the SQL view. These are similar to join logical files but SQL view logical files locate the access path at run time by use of the Query Definition Template and are not maintained by each join.
The strategic direction of IBM is to concentrate database performance improvement efforts on the newer SQL DDL defined database objects (tables, indexes, etc.) and to ignore the older legacy DDS defined objects (physical and logical files.)
// su file logici, mostra i campi chiave e OMIT
DSPFFD FILE(LIB1/MATPF0XL)
DSPFD FILE(LA_DAT/ANCL205L)
cerca sezione:
Descrizione scelta/omissione
Scelta dinamica . . . . . . . . . . . . . . : DYNSLT No
Numero di regole . . . . . . . . . . . . . : 2
Formato . . . . . . . . . . . . . . . . . . : ANCL2
Campo . . . . . . . . . . . . . . . . . . : CDAGE
Regola . . . . . . . . . . . . . . . . : OMIT
Comparazione . . . . . . . . . . . . : COMP EQ
Valore . . . . . . . . . . . . . . . . : ' '
Campo . . . . . . . . . . . . . . . . . . :
Regola . . . . . . . . . . . . . . . . : SELECT
Comparazione . . . . . . . . . . . . : ALL
iSeries V7R1
DSPFFD FILE(LIB/FILE)
oppure dal pannello Objects di SquirrelSQL
indici con SQL: from STRSQL
-- indici presenti SELECT * FROM qsys2.sysindexes WHERE index_schema='$lib' -- lista indici SELECT index_schema, index_name FROM qsys2.sysindexes WHERE table_schema='$lib'; -- lista indici SELECT i.table_schema, i.TABLE_NAME, i.Is_Unique, s.Index_Name,s.Index_Type, s.column_names FROM qsys2.SysIndexes i INNER JOIN qsys2.SysTableIndexStat s ON i.table_schema = s.table_schema AND i.TABLE_NAME = s.TABLE_NAME AND i.index_name = s.index_name AND s.table_schema = '$schema' AND s.TABLE_NAME = '$table' -- chiavi definite per l'indice SELECT KEYS.* FROM qsys2.sysindexes idx INNER JOIN qsys2.SYSKEYS KEYS ON (idx.index_name=KEYS.index_name) WHERE idx.index_schema='$lib' -- creare o eliminare indici DROP INDEX $lib.IDX01_FILE00; CREATE INDEX $lib.IDX01_FILE00 ON $lib.FILE00(MCDAGE, MCDCLI); GRANT SELECT ON $lib.FILE00 TO PUBLIC;
-- lista PK / contraints SELECT constraint_schema, constraint_name FROM qsys2.syscst WHERE table_schema='$lib' AND constraint_type IN ('PRIMARY KEY', 'UNIQUE');
If you need to create a logical file select/omit criteria, then you need to create a view. A view is not an index, though, and you can't mix an index and a view like you can with a keyed logical with select/omits. For that, a DDS spec is still the best. views:
CREATE VIEW myview1 AS SELECT lib FROM( SELECT rank() OVER(ORDER BY lib), lib FROM basetable) a
-- lista tabelle con descrizione SELECT TABLE_NAME,table_text,table_type FROM qsys2.systables WHERE table_schema = '$lib' AND table_type IN('t','l') SELECT * FROM sysibm.TABLES WHERE table_schema='$lib' SELECT TABLE_NAME, table_text FROM qsys2.systables WHERE table_schema='$lib' SELECT * FROM qsys2.systables WHERE table_schema='$lib' AND table_type='T' AND TABLE_NAME = '$table' -- lista campi, tipo e descrizioni SELECT * FROM qsys2.syscolumns WHERE table_schema = '$lib' AND TABLE_NAME='$table' -- info rilevanti SELECT column_name,data_type,LENGTH,column_text FROM qsys2.syscolumns WHERE table_schema = '$lib' AND TABLE_NAME='$table' -- vecchie versioni SELECT * FROM sysibm.COLUMNS WHERE table_schema = '$lib' AND TABLE_NAME='$table'
from squirrel use:
utility isql:
isql ODBC_NAME cat table.sql | isql ODBC_NAME # Each line in file.sql must contain exactly 1 SQL command except for the last line which must be blank
import export statements: (use metadata from SYSCAT.TABLES)
SELECT 'EXPORT TO /usr/data/SCHEMA/' || TABNAME || '.ixf OF IXF LOBS TO /usr/data/SCHEMA/lbos/ MODIFIED BY LOBSINFILE SELECT * FROM SCHEMA.' || TABNAME || ';' FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SCHEMA' ORDER BY TABNAME
SELECT 'IMPORT FROM /usr/data/SCHEMA/' || TABNAME || '.ixf OF IXF LOBS FROM /usr/data/SCHEMA/lobs/ MODIFIED BY LOBSINFILE INSERT INTO SCHEMA.' || TABNAME || ';' FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SCHEMA' ORDER BY TABNAME
Multiple lines: use the character "\".
SELECT * FROM TEST \ ORDER BY ID
List Structure of a Table
DESCRIBE SELECT * FROM [TABLE]
Table SQL commands get all of the tables of the database:
SELECT name, creator FROM sysibm.systables ORDER BY name
Create a Table:
CREATE TABLE <TABLE name>(<COLUMN name1> INTEGER, <COLUMN name2> CHAR(12), [etc...])
Sequence Numbers (see AutoNumber below) SQL commands:
-- Insert a row. INSERT INTO <TABLE name> VALUES (1,'hello') INSERT INTO <TABLE name> (<column1>, <column2) VALUES (<value1>, <value2>) -- Update fields. UPDATE <table> SET <field> = 'New info' WHERE <field> = 'Old Info'
autoincrement / Sequence Numbers
CREATE TABLE TESTING ( TEST_ID INTEGER NOT NULL generated always AS IDENTITY (START WITH 0, INCREMENT BY 1, no cache) PRIMARY KEY, NAME VARCHAR (30) )
String Concatenation
SELECT FIELD1 || ' ' || FIELD2 FROM TABLE; SELECT CONCAT(FIELD1, FIELD2) FROM TABLE;
Functions
WHERE DOUBLE(MYSTRING) > 0.0 -- converts a from a string to a double WHERE INTEGER(MYSTRING) > 0.0 -- converts from a string to an integer SELCT SUBSTR($string, $start, $length ) SELECT REPLACE($string, $search, $replace ); -- Lower/Upper Case Lower/Upper Case: On CHARACTER or VARCHAR LOWER(<fieldname>) LCASE(<filedname>) UPPER(<filedname>) UCASE(<filedname>) DIGITS -- returns a character representation of a number VARCHAR -- returns a varying-length representation of a string
Date/Time
CURRENT TIMESTAMP -- insert the current date/time as a TIMESTAMP INSERT INTO testing.TEST (TIMESTAMP) \ VALUES (CURRENT TIMESTAMP) CURRENT DATE CURRENT TIME -- Selecting current date and current time SELECT CURRENT DATE, CURRENT TIME FROM MYSCHEMA.MYTABLE -- Using current date, current time in insert query INSERT INTO MYSCHEMA.MYTABLE(MYDATE, MYTIME) VALUES (CURRENT TIME, CURRENT DATE)
-- Select records less than equal to a date ( <= ) SELECT DATEFIELD FROM db_lib.TSTFILE \ WHERE DATEFIELD <= '01/01/2000' -- range SELECT DATEFIELD FROM db_lib.TSTFILE \ WHERE DATEFIELD >= '06/01/2000' AND DATEFIELD <= '07/01/2000' -- range between AND $iDateOrder BETWEEN DATEFIELDI AND DATEFIELDF -- year function SELECT DATEFIELD FROM db_lib.TSTFILE \ WHERE YEAR(DATEFIELD) > 2000
Partita Iva: 02360450205
Content of this site is licensed under Creative Commons Attribution-Share Alike License.
