How to insert X rows into a table from a Select clause?

I'm trying to Insert 300.000 rows (or thereabouts) into a table (via QMF) using the following Select clause:

#
Insert into table_name
( Select row1, row2, row..x
from table_name2
Fetch first 300000 rows only);
#

The only message I get is that there's an error with the Fecth statement.

A friend told that this is happening due to the operating mode of the DB (Compatibility Mode and New function mode).

Is this true?
Is there anyway of getting a fixed number of rows into a table without usig the Fetch clause ?

Thanx in advance for any kind of help you can give me.

Loix

re: How to insert X rows into a table from a Select clause?

Hi Loix,

works great for me...

C:\Users\vijay>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09017" with
level identifier "02080107".
Informational tokens are "DB2 v9.1.700.855", "s090308", "WR21434", and Fix Pack
"7".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".


C:\Users\vijay>db2 connect to paulb001

Database Connection Information

Database server = DB2/NT 9.1.7
SQL authorization ID = VIJAY
Local database alias = PAULB001


C:\Users\vijay>db2 list tables for schema db2admin

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------

0 record(s) selected.


C:\Users\vijay>db2 list tables for schema syscat

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ATTRIBUTES SYSCAT V 2009-07-17-22.09.11.254002
BUFFERPOOLDBPARTITIONS SYSCAT V 2009-07-17-22.09.11.257001
BUFFERPOOLNODES SYSCAT V 2009-07-17-22.09.11.259002
BUFFERPOOLS SYSCAT V 2009-07-17-22.09.11.260003
CASTFUNCTIONS SYSCAT V 2009-07-17-22.09.11.262000
CHECKS SYSCAT V 2009-07-17-22.09.11.263001
COLAUTH SYSCAT V 2009-07-17-22.09.11.264003
COLCHECKS SYSCAT V 2009-07-17-22.09.11.265003
COLDIST SYSCAT V 2009-07-17-22.09.11.267001
COLGROUPCOLS SYSCAT V 2009-07-17-22.09.11.269003
COLGROUPDIST SYSCAT V 2009-07-17-22.09.11.270003
COLGROUPDISTCOUNTS SYSCAT V 2009-07-17-22.09.11.272003
COLGROUPS SYSCAT V 2009-07-17-22.09.11.268002
COLIDENTATTRIBUTES SYSCAT V 2009-07-17-22.09.11.329001
COLOPTIONS SYSCAT V 2009-07-17-22.09.11.276003
COLUMNS SYSCAT V 2009-07-17-22.09.11.280000
COLUSE SYSCAT V 2009-07-17-22.09.11.332000
CONSTDEP SYSCAT V 2009-07-17-22.09.11.333001
DATAPARTITIONEXPRESSION SYSCAT V 2009-07-17-22.09.11.336000
DATAPARTITIONS SYSCAT V 2009-07-17-22.09.11.337001
DATATYPES SYSCAT V 2009-07-17-22.09.11.340002
DBAUTH SYSCAT V 2009-07-17-22.09.11.342003
DBPARTITIONGROUPDEF SYSCAT V 2009-07-17-22.09.11.446001
DBPARTITIONGROUPS SYSCAT V 2009-07-17-22.09.11.447003
EVENTMONITORS SYSCAT V 2009-07-17-22.09.11.448003
EVENTS SYSCAT V 2009-07-17-22.09.11.450001
EVENTTABLES SYSCAT V 2009-07-17-22.09.11.483001
FULLHIERARCHIES SYSCAT V 2009-07-17-22.09.11.484003
FUNCDEP SYSCAT V 2009-07-17-22.09.11.486000
FUNCMAPOPTIONS SYSCAT V 2009-07-17-22.09.11.486004
FUNCMAPPARMOPTIONS SYSCAT V 2009-07-17-22.09.11.488001
FUNCMAPPINGS SYSCAT V 2009-07-17-22.09.11.489001
FUNCPARMS SYSCAT V 2009-07-17-22.09.11.490003
FUNCTIONS SYSCAT V 2009-07-17-22.09.11.494000
HIERARCHIES SYSCAT V 2009-07-17-22.09.11.497001
INDEXAUTH SYSCAT V 2009-07-17-22.09.11.498002
INDEXCOLUSE SYSCAT V 2009-07-17-22.09.11.499003
INDEXDEP SYSCAT V 2009-07-17-22.09.11.500003
INDEXES SYSCAT V 2009-07-17-22.09.11.501003
INDEXEXPLOITRULES SYSCAT V 2009-07-17-22.09.11.505001
INDEXEXTENSIONDEP SYSCAT V 2009-07-17-22.09.11.527002
INDEXEXTENSIONMETHODS SYSCAT V 2009-07-17-22.09.11.534002
INDEXEXTENSIONPARMS SYSCAT V 2009-07-17-22.09.11.536001
INDEXEXTENSIONS SYSCAT V 2009-07-17-22.09.11.517001
INDEXOPTIONS SYSCAT V 2009-07-17-22.09.11.537002
INDEXXMLPATTERNS SYSCAT V 2009-07-17-22.09.11.932000
KEYCOLUSE SYSCAT V 2009-07-17-22.09.11.538003
LIBRARIES SYSCAT V 2009-07-17-22.09.11.577001
LIBRARYAUTH SYSCAT V 2009-07-17-22.09.11.540000
LIBRARYBINDFILES SYSCAT V 2009-07-17-22.09.11.578003
LIBRARYVERSIONS SYSCAT V 2009-07-17-22.09.11.580001
NAMEMAPPINGS SYSCAT V 2009-07-17-22.09.11.581003
NICKNAMES SYSCAT V 2009-07-17-22.09.11.582003
NODEGROUPDEF SYSCAT V 2009-07-17-22.09.11.585001
NODEGROUPS SYSCAT V 2009-07-17-22.09.11.598001
PACKAGEAUTH SYSCAT V 2009-07-17-22.09.11.599003
PACKAGEDEP SYSCAT V 2009-07-17-22.09.11.600003
PACKAGES SYSCAT V 2009-07-17-22.09.11.602001
PARTITIONMAPS SYSCAT V 2009-07-17-22.09.11.604002
PASSTHRUAUTH SYSCAT V 2009-07-17-22.09.11.606001
PREDICATESPECS SYSCAT V 2009-07-17-22.09.11.607002
PROCEDURES SYSCAT V 2009-07-17-22.09.11.609000
PROCPARMS SYSCAT V 2009-07-17-22.09.11.622003
REFERENCES SYSCAT V 2009-07-17-22.09.11.624001
ROUTINEAUTH SYSCAT V 2009-07-17-22.09.11.625001
ROUTINEDEP SYSCAT V 2009-07-17-22.09.11.634002
ROUTINEOPTIONS SYSCAT V 2009-07-17-22.09.11.613003
ROUTINEPARMOPTIONS SYSCAT V 2009-07-17-22.09.11.620003
ROUTINEPARMS SYSCAT V 2009-07-17-22.09.11.632002
ROUTINES SYSCAT V 2009-07-17-22.09.11.626003
ROUTINESFEDERATED SYSCAT V 2009-07-17-22.09.11.630002
SCHEMAAUTH SYSCAT V 2009-07-17-22.09.11.635001
SCHEMATA SYSCAT V 2009-07-17-22.09.11.636003
SECURITYLABELACCESS SYSCAT V 2009-07-17-22.09.11.637002
SECURITYLABELCOMPONENTELEMENTS SYSCAT V 2009-07-17-22.09.11.638003
SECURITYLABELCOMPONENTS SYSCAT V 2009-07-17-22.09.11.640001
SECURITYLABELS SYSCAT V 2009-07-17-22.09.11.665003
SECURITYPOLICIES SYSCAT V 2009-07-17-22.09.11.704001
SECURITYPOLICYCOMPONENTRULES SYSCAT V 2009-07-17-22.09.11.701001
SECURITYPOLICYEXEMPTIONS SYSCAT V 2009-07-17-22.09.11.702003
SEQUENCEAUTH SYSCAT V 2009-07-17-22.09.11.757001
SEQUENCES SYSCAT V 2009-07-17-22.09.11.755000
SERVEROPTIONS SYSCAT V 2009-07-17-22.09.11.761001
SERVERS SYSCAT V 2009-07-17-22.09.11.778002
STATEMENTS SYSCAT V 2009-07-17-22.09.11.805003
SURROGATEAUTHIDS SYSCAT V 2009-07-17-22.09.11.807001
TABAUTH SYSCAT V 2009-07-17-22.09.11.808003
TABCONST SYSCAT V 2009-07-17-22.09.11.810002
TABDEP SYSCAT V 2009-07-17-22.09.11.816000
TABDETACHEDDEP SYSCAT V 2009-07-17-22.09.11.817001
TABLES SYSCAT V 2009-07-17-22.09.11.818002
TABLESPACES SYSCAT V 2009-07-17-22.09.11.822001
TABOPTIONS SYSCAT V 2009-07-17-22.09.11.823003
TBSPACEAUTH SYSCAT V 2009-07-17-22.09.11.824003
TRANSFORMS SYSCAT V 2009-07-17-22.09.11.826002
TRIGDEP SYSCAT V 2009-07-17-22.09.11.828001
TRIGGERS SYSCAT V 2009-07-17-22.09.11.829001
TYPEMAPPINGS SYSCAT V 2009-07-17-22.09.11.842003
USEROPTIONS SYSCAT V 2009-07-17-22.09.11.844003
VIEWDEP SYSCAT V 2009-07-17-22.09.11.846001
VIEWS SYSCAT V 2009-07-17-22.09.11.847001
WRAPOPTIONS SYSCAT V 2009-07-17-22.09.11.848003
WRAPPERS SYSCAT V 2009-07-17-22.09.11.851002
XDBMAPGRAPHS SYSCAT V 2009-07-17-22.09.11.933003
XDBMAPSHREDTREES SYSCAT V 2009-07-17-22.09.11.935002
XSROBJECTAUTH SYSCAT V 2009-07-17-22.09.11.859000
XSROBJECTCOMPONENTS SYSCAT V 2009-07-17-22.09.11.856003
XSROBJECTDEP SYSCAT V 2009-07-17-22.09.11.937000
XSROBJECTHIERARCHIES SYSCAT V 2009-07-17-22.09.11.860001
XSROBJECTS SYSCAT V 2009-07-17-22.09.11.853000

110 record(s) selected.


C:\Users\vijay>db2 select count(*) from syscat.columns

1
-----------
4250

1 record(s) selected.


C:\Users\vijay>db2 create table db2admin.columns like syscat.columns
DB20000I The SQL command completed successfully.

C:\Users\vijay>db2 describe table db2admin.columns

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
TABSCHEMA SYSIBM VARCHAR 128 0 No
TABNAME SYSIBM VARCHAR 128 0 No
COLNAME SYSIBM VARCHAR 128 0 No
COLNO SYSIBM SMALLINT 2 0 No
TYPESCHEMA SYSIBM VARCHAR 128 0 No
TYPENAME SYSIBM VARCHAR 128 0 No
LENGTH SYSIBM INTEGER 4 0 No
SCALE SYSIBM SMALLINT 2 0 No
DEFAULT SYSIBM VARCHAR 254 0 Yes
NULLS SYSIBM CHARACTER 1 0 No
CODEPAGE SYSIBM SMALLINT 2 0 No
LOGGED SYSIBM CHARACTER 1 0 No
COMPACT SYSIBM CHARACTER 1 0 No
COLCARD SYSIBM BIGINT 8 0 No
HIGH2KEY SYSIBM VARCHAR 254 0 Yes
LOW2KEY SYSIBM VARCHAR 254 0 Yes
AVGCOLLEN SYSIBM INTEGER 4 0 No
KEYSEQ SYSIBM SMALLINT 2 0 Yes
PARTKEYSEQ SYSIBM SMALLINT 2 0 Yes
NQUANTILES SYSIBM SMALLINT 2 0 No
NMOSTFREQ SYSIBM SMALLINT 2 0 No
NUMNULLS SYSIBM BIGINT 8 0 No
TARGET_TYPESCHEMA SYSIBM VARCHAR 128 0 Yes
TARGET_TYPENAME SYSIBM VARCHAR 128 0 Yes
SCOPE_TABSCHEMA SYSIBM VARCHAR 128 0 Yes
SCOPE_TABNAME SYSIBM VARCHAR 128 0 Yes
SOURCE_TABSCHEMA SYSIBM VARCHAR 128 0 Yes
SOURCE_TABNAME SYSIBM VARCHAR 128 0 Yes
DL_FEATURES SYSIBM CHARACTER 10 0 Yes
SPECIAL_PROPS SYSIBM CHARACTER 8 0 Yes
HIDDEN SYSIBM CHARACTER 1 0 No
INLINE_LENGTH SYSIBM INTEGER 4 0 No
IDENTITY SYSIBM CHARACTER 1 0 No
GENERATED SYSIBM CHARACTER 1 0 No
TEXT SYSIBM CLOB 2097154 0 Yes
COMPRESS SYSIBM CHARACTER 1 0 No
AVGDISTINCTPERPAGE SYSIBM DOUBLE 8 0 Yes
PAGEVARIANCERATIO SYSIBM DOUBLE 8 0 Yes
SUB_COUNT SYSIBM SMALLINT 2 0 No
SUB_DELIM_LENGTH SYSIBM SMALLINT 2 0 No
IMPLICITVALUE SYSIBM VARCHAR 254 0 Yes
SECLABELNAME SYSIBM VARCHAR 128 0 Yes
REMARKS SYSIBM VARCHAR 254 0 Yes

43 record(s) selected.


C:\Users\vijay>db2 select count(*) from syscat.columns

1
-----------
4293

1 record(s) selected.


C:\Users\vijay>db2 insert into db2admin.columns select * from syscat.columns fetch first 100 rows only
DB20000I The SQL command completed successfully.

C:\Users\vijay>db2 select count(*) from db2admin.columns

1
-----------
100

1 record(s) selected.

C:\Users\vijay>db2 insert into db2admin.columns select * from syscat.columns fetch first 1000 rows only
DB20000I The SQL command completed successfully.

C:\Users\vijay>db2 select count(*) from db2admin.columns

1
-----------
1100

1 record(s) selected.