Trucs et Astuces - AS400 - Tips and Tricks
page d'accueil Boite à outils
Table des matières table of Contains
la table sql AS400 qui fait toujours une ligne s'appelle qsys2.qsqptabl. Et aussi sysibm.sysdummy1. Pratique pour tester les fonctions intégrées
sql limité : 'select * from makep fetch first 10 rows only'
création de table par référence à une table dictionnaire : create table as (select ... from ...,...) with no data ; label on ...
Glané sur le web
I'd rather prefer select * from master join (select * from billfille) as billfile on key1 = k1 and key2 = k2
à http://search400.techtarget.com/tip/1,289483,sid3_gci1097043,00.html?FromTaxonomy=%2Fpr%2F2f9Speed up SQL subselects
10 Jun 2005
An SQL subselect picks records based on whether a value appears in another select -- a simple example would be:
select * from orders where partno in (select partno from outofstock) .
My master file has two keys so to do a subselect I have to to combine them. Key1 is 9s0, key2 is 3s0 so I have been selecting like this:
select * from master where key1*1000+key2 in (select k1*1000+k2 from billfile)
This is very slow, compared to an inner join. I recently found that if I converted the combined key to a string, response is as fast as an inner join (apparently the iSeries is much better with characters than math):select * from master where cast(key1 as char(9))||cast(key2 as char(3))in (select cast(k1 as char(9))||cast(k2 as char(3)) from billfile)
The || is string concatenation in SQL; cast() transforms one data type to another.
there is a very simple solution : for example, look at this SQL that produces a wide range of SQL column type
Have a Question on CPYTOIMPF converting to csv. For the Numeric fields, the leading Zeros are removed (which is fine).. but the trailing zeros are shown as blanks & the comma separator is shown at the length of the Numeric field.
Is there a way to supress trailing Zero/Blank to show the Numeric data
Thanks in Advance.
char(current date,iso) concat '</mydate><mytime>' concat
char(current time, iso) concat '</mytime><mystamp>' concat
char(current timestamp) concat '</mystamp><user>' concat
user concat '</user><mydecimal>' concat
char(decimal(days(current date), 7, 0)) concat '</mydecimal><myzoned>' concat
char(zoned(days(current date), 7, 0)) concat '</myzoned><mydouble>' concat
char(double(days(current date) ** 12 )) concat '</mydouble><mybigint>' concat
char(bigint(days(current date) ** 3 )) concat '</mybigint>
MISSING : XML HEADER .......................................AJOUTER LES ENTETES XML
See also QSH DB2, its exports are CSV
select chgcod,Here's the result set.
sum(case state when 'NY' then 1 else 0 end) as New_York,
sum(case state when 'MN' then 1 else 0 end) as Minnesota,
sum(case when state not in ('NY','MN')
then 1 else 0 end) as Other
group by chgcod
order by chgcod
CHGCOD NEW_YORK MINNESOTA OTHER
1 2 0 3
2 1 0 3
3 0 2 1
Update filea as a
set (clasaa,slmnaa) =
(select classbb,slmnbb from fileb as b
where b.divnbb = a.divnaa
and b.pnumbb = a.pnumbb),
(select * from fileb as b
where b.divnbb = a.divnaa
and b.pnumbb = a.pnumbb);
Found on the web :
CL has been able to do a few forms of writing to files for many years. CL can call the Sort (QLGSORT) API and pass records into the API through the input buffer -- the API can send those to output files which can be copied/appended to other files. CL can call QM queries to effect SQL INSERTs to files.
But in Version 5 of OS/400, CL got even closer through the QShell DB2 utility (see example #1). And with a little research, you can see that the DB2 utility is a symbolic link to program QZDFMDB2 (see example #2).
Still not perfect, but pretty workable.
#1- Tom Liotta
QSH CMD('db2 "INSERT INTO QIWS.QCUSTCDT (CUSNUM, LSTNAM, INIT) VALUES(876543, ''Liotta'', '')"')
CALL QZDFMDB2 ('INSERT INTO TOML.QCUSTCDT (CUSNUM, LSTNAM, INIT) VALUES(876543, ''Liotta'', ''T'') ')
QZDFMDB2 is a pretty and native solution, but there is a little processor overhead to load and close the QSH.Will have to check if errors can be MONMSGed. Seems a better solution to use it from QSH : QSH interface is published, QZDFMDB2 is an IBM internal tool, interface can change without advertissement.
An other pretty property of QSH DB2 : it's standard output when running a SELECT is a CSV file. Have found a sample at iseries network, lost the link
Here is a complete sample based on previous extract
then with STRSQL, work with the file :
select * from jpl/tenlibs +F4
Now, build a client access DTF to read the file, the simplest dtf : choose the file and that's all (tenlibs.dtf). Open it from Client-Access excel-addin tool bar : tenlibs.xls
And direct 400 to excel also
Now, work on colum heading and column text Nota : column heading is a one-string value, 60 chars length, showed by SQL as 3 * 20 chars
LABEL ON COLUMN JPL/TENLIBS (
LABEL ON COLUMN JPL/TENLIBS (
re run the client acces dtf (tenlibs.dtf) Client Access DTF + excel : column heading continues to be the column name : Client Access don't take care of column heading
But direct 400 to excel yes : SQL2SYLK SQL('select * from jpl/tenlibs') IFSFILE('TenLibsLabel.slk') TenLibsLabel.slk
Update the DTF : rename the columns
Now, Client Access shows correct headings tenlibs.xls
litteral or constants ? at first, it's the same thing
select myfunc('string') from qsys2.qsqptabl is equal to select myfunc(cast('string' as varchar)) from qsys2.qsqptabl
string litterals are automatically casted to varchar
select myfunc(0) from qsys2.qsqptabl is equal to ?
Built'in SQL Data type groups
datalink : datalink
datetime : time timestamp date
character string : char varchar clob
graphic string : graphic vargraphic dblob
binary string : binary varbinary blob
numeric : smallint integer bigint decimal numeric real float
rowid : rowid
details and samples : in sql reference (rbafzmst.pdf) chapter Language elements / Constants
up to 19 digits, no decimal point, minus sign -> an integer (bigint ! 20I 0)
2 numbers separated by a E : float 4F or 8F
up to 63, decimal point -> decimal
char -> varying
graphic -> varying
bin -> varying
time timestamp date -> time timestamp date
http://www.itjungle.com/fhg/fhg031704-story02.html shows RPG declarations for SQL data types
RPG, dynamic WHERE, fixed SELECT
In this sample, the format of the returned rows are fixed at compile time
, * this sample shows how to code in RPG the inside-code of a
PLSQL, fixed SQL statement
CREATE PROCEDURE LAMONTRE1/READRSP1 ()
PLSQL, dynamic Statement
-- DROP PROCEDURE LAMONTRE1.READRSP2 ;
I've not try to change the list of selected columns, but I will test that soon
I'm more fluent in IBMi than in english. So, don't hesitate to send me back any page or excerpt that may need a correction.
This tool is a free source text; you can redistribute it and/or modify it as much as you need. Just add as comment in the code a reference to this site : http://jplamontre.free.fr/jpltools.htm ... and send me an email Contact with "jpltools" in the subject (it's for my antispam rules). I will be happy to know what you have done with the jpltools.This tool is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY :
THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.