Use these LIBNAME statements
for all examples.
libname db2lib db2;
libname shlib db2 connection=shared;
data db2lib.table1 (bulkload=yes);
x=1;
name='Tom';
run;
data shlib.table1
(bulkload=yes bl_db2tblxst=yes bl_db2ldct1='RESUME YES');
set shlib.table1;
run;
Replace Table1 with
itself.
data shlib.table1
(bulkload=yes bl_db2tblxst=yes bd_db2ldct1='REPLACE');
set shlib.table1;
run;
Load DB2 tables directly
from other objects.
data db2lib.emp (bulkload=yes);
bl_db2ldct1='replace log no nocopypend'
bl_db2cursor='select * from dsn8710.emp');
set db2lib.emp (obs=0);
run;
You can also use this
option in a PROC SQL statement to load DB2 tables directly from other
objects, as shown below.
options sastrace=',,,d';
libname db2lib db2 authid=dsn8710;
libname mylib db2;
proc datasets library=mylib;
delete emp;run;
proc sql;
connect to db2;
create table mylib.emp
(BULKLOAD=YES
BL_DB2LDCT1='REPLACE LOG NO NOCOPYPEND'
BL_DB2CURSOR='SELECT FIRSTNAME, LASTNAME, WORKDEPT,
HIREDATE, JOB, SALARY, BONUS, COMM
FROM DSN8710.EMP')
as select firstname, lastname, workdept,
hiredate, job, salary, bonus, comm
from db2lib.emp (obs=0);
quit;
Here is another similar
example.
options sastrace=',,,d';
libname db2lib db2 authid=dsn8710;
libname mylib db2;
proc datasets library=mylib;
delete emp;run;
proc sql;
connect to db2;
create table mylib.emp
(BULKLOAD=YES
BL_DB2LDCT1='REPLACE LOG NO NOCOPYPEND'
BL_DB2CURSOR='SELECT FIRSTNAME, LASTNAME, WORKDEPT,
HIREDATE, JOB, SALARY, BONUS, COMM
FROM DSN8710.EMP'
BL_DB2LDCT3='RUNSTATS TABLESPACE DSNDB04.TEMPTTABL
TABLE(ALL) INDEX(ALL) REPORT YES')
as select firstname, lastname, workdept,
hiredate, job, salary, bonus, comm
from db2lib.emp (obs=0);
quit;
Generate control and
data files, create the table, but do not run the utility to load it.
data shlib.table2 (bulkload=yes
bl_db2ldext=genonly bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
set shlib.table1;
run;
Use the control and
data files that you generated in the preceding example load the table.
The OBS=1 data set option on the input file prevents the DATA step
from reading the whole file. Because the data is really in SysRec,
you need only the input file to satisfy the engine.
data db2lib.table2 (bulkload=yes bl_db2tblxst=yes
bl_db2ldext=userun bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
set db2lib.table1 (obs=1);
run;
A more efficient approach
than the previous example is to eliminate going to DB2 to read even
one observation from the input table. This also means that the DATA
step processes only one observation, without any input I/O. Note that
the one variable V is not on the table. Any variables listed here
(there is no need for more than one), are irrelevant because the table
already exists; they are not used.
data db2lib.table2 (bulkload=yes bl_db2tblxst=yes
bl_db2ldext=userun bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
v=0;
run;
Generate control and
data files, but do not create the table or run the utility. Setting
BL_DB2TBLXST=YES when the table does not exist prevents you from creating
the table; this only makes sense because you are not going to load
any data into the table at this time.
data db2lib.table3 (bulkload=yes bl_db2tblxst=yes
bl_db2ldext=genonly bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
set db2lib.table1;
run;
Use the control and
data files that you generated in the preceding example to load the
table. The OBS=1 data set option on the input file prevents the DATA
step from reading the whole file. In this case, you must specify the
input file because it contains the column definitions that are necessary
to create the table.
data shlib.table3 (bulkload=yes bl_db2ldext=userun
bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
set shlib.table1 (obs=1);
run;
If you know the column
names, a more efficient approach than the previous example is to eliminate
going to DB2 to obtain the column definitions. In this case, the variable
names and data types must match, because they are used to create the
table. However, the values specified for the variables are not included
on the table, because all data to load comes from the existing SysRec.
data db2lib.table3 (bulkload=yes bl_db2ldext=userun
bl_db2in='userid.sysin' bl_db2rec='userid.sysrec');
x=0;
name='???';
run;
You can use other applications
that do output processing.
data work.a;
x=1;
run;
proc sql;
create db2lib.table4 (bulkload=yes) as select * from a;
quit;