[GENERAL] Migrating data from DB2 zOS to PostgreSQL
Hello, We need some help on how we can migrate data from DB2 zOS database to postgres database. Are there any utilities present? Any thoughts how we should approach? -- Thanks & Regards, Swapnil Vaze
Re: [GENERAL] Migrating data from DB2 zOS to PostgreSQL
Hello Julien, We created DDLs from DB2 zOS system and tried to run script in below format: ./db2topg.pl -f sql1.txt -o testdir It throws below error: I don't understand at ./db2topg.pl line 880, line 24. For testing we used file with below content: cat sql1.txt -- This CLP file was created using DB2LOOK Version "10.5" -- Timestamp: Tue Dec 6 04:14:28 CST 2016 -- Database Name: DB239 -- Database Manager Version: DB2 Version 11.1.0 -- Database Codepage: 1208 -- DDL Statements for Table "A90DVDT"."DLR_FAM_MRKTSHR_FY_END" CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END" ( "DEALER_ID" CHAR(6) NOT NULL , "MKTSHR_MONTH" DATE NOT NULL , "L12_DP_DLR_IN_AOR" DECIMAL(15,6) , "L12_DP_DLR_OUT_AOR" DECIMAL(15,6) , "L12_DP_DLR_SHARE" DECIMAL(8,5) , "L12_SA_DLR_SHARE" DECIMAL(8,5) , "L12_CA_DLR_SHARE" DECIMAL(8,5) , "L12_U90_DLR_IN_AOR" DECIMAL(15,6) , "L12_U90_DLR_OUT_AOR" DECIMAL(15,6) , "L12_U90_DLR_SHARE" DECIMAL(8,5) ); Thanks, Swapnil On Tue, Dec 6, 2016 at 12:23 PM, Julien Rouhaud wrote: > On Tue, Dec 06, 2016 at 11:06:12AM +0530, Swapnil Vaze wrote: > > Hello, > > > > Hello > > > We need some help on how we can migrate data from DB2 zOS database to > > postgres database. > > > > Are there any utilities present? Any thoughts how we should approach? > > You can use this utility: https://github.com/dalibo/db2topg > > The README should provide all needed informations. > > -- > Julien Rouhaud > http://dalibo.com - http://dalibo.org > -- Thanks & Regards, Swapnil Vaze
[GENERAL] Accessing DB2 tables from postgresql
Hello, I am trying to access few table present in DB2 LUW from postgres9.5 database. I have installed unixODBC driver and connection to DB2 is working fine. I have installed CartoDB/odbc_fdw foreign data wrappers. I have user below commands to create foreign table: $ create extension odbc_fdw; $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 options(odbc_UID '', odbc_PWD ''); $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database 'TESTV9', schema 'schema1', table 'table1' ); All commands work fine, however when I try to select data from table it throws error: $ select * from odbc_table; ERROR: Executing ODBC query Can anyone help me here? How can I access DB2 LUW or zOS database tables from postgres? -- Thanks & Regards, Swapnil Vaze
Re: [GENERAL] Accessing DB2 tables from postgresql
Hello, I dropped and recreated foreign table with odbc_database option. Also tried to use import foreign schema object still getting same error. CREATE FOREIGN TABLE odbc_table ( CTGRY_ID INTEGER, CTGRY_CD VARCHAR(10), UPDT_TS TIMESTAMP ) SERVER odbc_server OPTIONS ( odbc_database 'TESTV9', schema 'u90nmqd', sql_query 'select CTGRY_ID,CTGRY_CD,UPDT_TS from `schema1`.`table1`', sql_count 'select count(CTGRY_ID) from `schema1`.`table1`' ); I have created server with below definition: CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'TESTV9'); To test for other DBMS, I created foreign object for postgres with odbc_fdw, however it is throwing too long encoding error. Does ODBC wrapper support DB2 access? Thanks, Swapnil Vaze On Tue, Jun 27, 2017 at 6:38 PM, Adrian Klaver wrote: > On 06/27/2017 03:11 AM, Swapnil Vaze wrote: > >> Hello, >> >> I am trying to access few table present in DB2 LUW from postgres9.5 >> database. >> >> I have installed unixODBC driver and connection to DB2 is working fine. >> I have installed CartoDB/odbc_fdw foreign data wrappers. >> I have user below commands to create foreign table: >> $ create extension odbc_fdw; >> $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 >> options(odbc_UID '', odbc_PWD ''); >> $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD >> VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS (database >> 'TESTV9', schema 'schema1', table 'table1' ); >> >> All commands work fine, however when I try to select data from table it >> throws error: >> $ select * from odbc_table; >> ERROR: Executing ODBC query >> >> Can anyone help me here? >> > > Might want to take a look at: > > https://github.com/CartoDB/odbc_fdw > > From the examples above you need to prefix some settings with odbc_. In > your example that would be odbc_database 'TESTV9' instead of database > 'TESTV9'. > > > How can I access DB2 LUW or zOS database tables from postgres? >> >> -- >> Thanks & Regards, >> Swapnil Vaze >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Thanks & Regards, Swapnil Vaze
Re: [GENERAL] Accessing DB2 tables from postgresql
Hello, Thanks for help!! I am following up on that existing thread. Thanks, Swapnil Vaze On Wed, Jun 28, 2017 at 7:01 PM, Adrian Klaver wrote: > On 06/28/2017 01:28 AM, Swapnil Vaze wrote: > >> Hello, >> >> I dropped and recreated foreign table with odbc_database option. Also >> tried to use import foreign schema object still getting same error. >> >> CREATE FOREIGN TABLE >>odbc_table ( >> CTGRY_ID INTEGER, >> CTGRY_CD VARCHAR(10), >> UPDT_TS TIMESTAMP >>) >>SERVER odbc_server >>OPTIONS ( >> odbc_database 'TESTV9', >> schema 'u90nmqd', >> sql_query 'select CTGRY_ID,CTGRY_CD,UPDT_TS from `schema1`.`table1`', >> sql_count 'select count(CTGRY_ID) from `schema1`.`table1`' >>); >> >> I have created server with below definition: >> CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn >> 'TESTV9'); >> > > So do you have a DSN named 'TESTV9' on the system you are connecting from? > > If not you will need to use a driver name instead of DSN. > > >> To test for other DBMS, I created foreign object for postgres with >> odbc_fdw, however it is throwing too long encoding error. >> > > What is the exact error mesage? > > >> Does ODBC wrapper support DB2 access? >> > > That would seem to depend on whether you have a DB2 ODBC driver present on > your machine: > > https://github.com/CartoDB/odbc_fdw > > "To make use of the extension ODBC drivers for the data sources to be used > must be installed in the system and reflected in the /etc/odbcinst.ini > file." > > See also: > > https://github.com/CartoDB/odbc_fdw/issues/45 > > I would ask there, by either responding to existing issue or starting a > new issue. > > > >> Thanks, >> Swapnil Vaze >> >> >> On Tue, Jun 27, 2017 at 6:38 PM, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 06/27/2017 03:11 AM, Swapnil Vaze wrote: >> >> Hello, >> >> I am trying to access few table present in DB2 LUW from >> postgres9.5 database. >> >> I have installed unixODBC driver and connection to DB2 is >> working fine. >> I have installed CartoDB/odbc_fdw foreign data wrappers. >> I have user below commands to create foreign table: >> $ create extension odbc_fdw; >> $ CREATE USER MAPPING FOR postgres SERVER odbc_server_db2 >> options(odbc_UID '', odbc_PWD ''); >> $ CREATE FOREIGN TABLE odbc_table (CTGRY_ID INTEGER, CTGRY_CD >> VARCHAR(10),UPDT_TS TIMESTAMP) SERVER odbc_server_db2 OPTIONS >> (database 'TESTV9', schema 'schema1', table 'table1' ); >> >> All commands work fine, however when I try to select data from >> table it throws error: >> $ select * from odbc_table; >> ERROR: Executing ODBC query >> >> Can anyone help me here? >> >> >> Might want to take a look at: >> >> https://github.com/CartoDB/odbc_fdw >> <https://github.com/CartoDB/odbc_fdw> >> >> From the examples above you need to prefix some settings with >> odbc_. In your example that would be odbc_database 'TESTV9' instead >> of database 'TESTV9'. >> >> >> How can I access DB2 LUW or zOS database tables from postgres? >> >> -- Thanks & Regards, >> Swapnil Vaze >> >> >> >> -- Adrian Klaver >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> >> >> -- >> Thanks & Regards, >> Swapnil Vaze >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Thanks & Regards, Swapnil Vaze
