BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
huks
Fluorite | Level 6

Good morning,
I have several SAS tables containing several million records that I would like to load into DB2. For now, I do it via the NODE DATA Target (Insert) but given the amount of data, I would like to do a LOAD instead of an INSERT.

Could someone help me?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
VincentRejany
SAS Employee

Hi

This is the same node for inserting and loading. Bulk load is supported for DB2, you can refer to the following entry in the doc: https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.9/dfUnity.html#dfU_PFOutput_TrgtInsert....

This allows you to process a set number of rows at a time. Enter the number of rows to be processed in the Property Value field when you have thousands of records. This option will help your system run more efficiently. The default value for the BULK_ROW_COUNT option is 10,000.

 

Note: Bulk row count is supported on databases such as Oracle SQL Server and DB2. There is no need to activate a bulkload option at the driver level in the data connection for the table.

 

View solution in original post

3 REPLIES 3
VincentRejany
SAS Employee

Hi

This is the same node for inserting and loading. Bulk load is supported for DB2, you can refer to the following entry in the doc: https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.9/dfUnity.html#dfU_PFOutput_TrgtInsert....

This allows you to process a set number of rows at a time. Enter the number of rows to be processed in the Property Value field when you have thousands of records. This option will help your system run more efficiently. The default value for the BULK_ROW_COUNT option is 10,000.

 

Note: Bulk row count is supported on databases such as Oracle SQL Server and DB2. There is no need to activate a bulkload option at the driver level in the data connection for the table.

 

huks
Fluorite | Level 6
I already tried that but it seems that the command is still an insert not a load thanks
RacheLGomez123
Fluorite | Level 6
You can load SAS tables into a DB2 table using the following steps:

Export the SAS table to a flat file in a format that can be easily imported into DB2. For example, you could export the SAS table as a comma-separated values (CSV) file.

Create a table in DB2 that has the same structure as the SAS table. You can do this using SQL commands or a database management tool.

Use the LOAD utility in DB2 to load the data from the flat file into the DB2 table. The exact syntax of the LOAD command will depend on the format of the flat file and the structure of the DB2 table.

Here is an example of a LOAD command that can be used to load a CSV file into a DB2 table:

vbnet
Copy code
LOAD FROM /path/to/csvfile.csv OF DEL MODIFIED BY NOCHARDEL INSERT INTO mydb.mytable
In this example, "/path/to/csvfile.csv" is the path to the CSV file, "mydb.mytable" is the name of the DB2 table, and "NOCHARDEL" indicates that the delimiter used in the CSV file is a comma.

Once the data is loaded into the DB2 table, you can use SQL commands to query and manipulate the data as needed.

Regards,
Rachel Gomez

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1075 views
  • 0 likes
  • 3 in conversation