Geek asks and answers

Uploading data from one table to another located in different databases - Netezza

I have a large file that I uploaded to a table in a netezza database using the ETL tool, allows you to call this database Staging_DB. Now publish some checks, the contents of this table must be inserted into a similar structured table located in another netezza DB , let's call it PROD_DB. What is the fastest way to transfer data from Staging_DBto PROD_DB?

  • Should I use the ETL tool to load data into PROD_DB? Or,
  • Should the transfer be made using the concept of external tables?
+3
source share
6 answers

, - . Netezza, Netezza - , .

-

dbname.schemaname(loggenin_username)=> \dpu username

-

INSERT INTO Staging_DB..TBL1 SELECT * FROM PROD_DB..TBL1

- , , , UDT ( ).

, .

+1

- Transient External Tables. /db. Netezza Netezza, , internal.

CREATE EXTERNAL TABLE 'C:\FileName.dat'
USING (
delim 167
datestyle 'MDY'
datedelim '/'
maxerrors 2
encoding 'internal'
Compress True
REMOTESOURCE 'ODBC'
logDir 'c:\' )  AS
SELECT * FROM source_table;

, DDL .

INSERT INTO target SELECT * FROM external  'C:\FileName.dat'
USING (
delim 167
datestyle 'MDY'
datedelim '/'
maxerrors 2
encoding 'internal'
Compress True
REMOTESOURCE 'ODBC'
logDir 'c:\' );
+1

SP db CTAS . SP - . - NZ, Netezza, , .

0

SQL-,

INSERT INTO Staging_DB..TBL1 SELECT * FROM PROD_DB..TBL1

, .

, ,

HY0000: " Cross Database Access "

even if you have read and write access to databases and tables.

0
source

In most cases, you can simply change the directory using the "Install Directory" command

https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.0.3/com.ibm.nz.dbu.doc/r_dbuser_set_catalog.html

0
source 
set catalog='database_name';
insert into target_db.target_schema.target_table select source_db.source_schema.source_table;
0
source

More articles:


All Articles