dalibo / db2topg Goto Github PK

Hi darold,
we need your kind support to analyse the error when we run the show report command.

FATAL: ORA-03135: connection lost contact
Process ID: 28097
Session ID: 2661 Serial number: 425 (DBD ERROR: error possibly near <> indicator at char 375 in 'SELECT
CONS.TABLE_NAME,
CONS.CONSTRAINT_NAME,
COLS.COLUMN_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
CONS.R_CONSTRAINT_NAME,
COLS_R.COLUMN_NAME R_COLUMN_NAME,
CONS.SEARCH_CONDITION,CONS.DELETE_RULE,CONS.DEFERRABLE,CONS.DEFERRED,
CONS.OWNER,CONS.R_OWNER,
COLS.POSITION,COLS_R.POSITION,
CONS.VALIDATED
FROM ALL_CONSTRAINTS CONS
LEFT JOIN <>ALL_CONS_COLUMNS COLS ON (COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME AND COLS.OWNER = CONS.OWNER AND COLS.TABLE_NAME = CONS.TABLE_NAME)
LEFT JOIN ALL_CONSTRAINTS CONS_R ON (CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME AND CONS_R.OWNER = CONS.R_OWNER)
LEFT JOIN ALL_CONS_COLUMNS COLS_R ON (COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME AND COLS_R.POSITION=COLS.POSITION AND COLS_R.OWNER = CONS.R_OWNER)
WHERE CONS.CONSTRAINT_TYPE = 'R' AND CONS.OWNER = 'PMS' AND ( NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p1) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p2)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p3) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p4) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p5)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p6) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p7) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p8)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p9) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p10) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p11)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p12) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p13) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p14)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p15) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p16) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p17)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p18) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p19) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p20)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p21) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p22) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p23)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p24) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p25) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p26)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p27) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p28) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p29)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p30) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p31) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p32)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p33) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p34) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p35)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p36) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p37) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p38)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p39) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p40) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p41)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p42) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p43) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p44)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p45) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p46) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p47)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p48) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p49) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p50)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p51) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p52) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p53)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p54) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p55) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p56)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p57) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p58) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p59)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p60) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p61) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p62)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p63) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p64) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p65)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p66) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p67) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p68)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p69) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p70) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p71)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p72) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p73) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p74)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p75) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p76) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p77)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p78) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p79) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p80)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p81) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p82) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p83)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p84) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p85) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p86)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p87) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p88) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p89)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p90) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p91) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p92)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p93) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p94) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p95)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p96) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p97) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p98)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p99) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p100) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p101)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p102) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p103) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p104)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p105) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p106) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p107)
AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p108) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p109) AND NOT REGEXP_LIKE(UPPER(CONS.TABLE_NAME), :p110))
ORDER BY CONS.TABLE_NAME, CONS.CONSTRAINT_NAME, COLS.POSITION
')

C:\ora2pg>ora2pg -t SHOW_VERSION -c C:\ora2pg\TestSep2021_PMS\config\ora2pg.conf
FATAL: 12170 ... ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach)

C:\ora2pg>------------------------------Error Message---------------------------------------
WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11
DBI connect('host=XXXX;Service_Name=XXXX;port=1530','XXXX',...) failed: ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach) at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1624.
Aborting export...

getting below error .

user@hsotname[db2topg-master] $ ./db2topg.pl -f /tmp/db2mig/pksql_script.sql -o /tmp/db2mig/XXX -d DBNAME -IT -u db2user -p xxxxxx

Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1019.
Global symbol "$leftover" requires explicit package name at ./db2topg.pl line 1021.
Global symbol "$leftover" requires explicit package name at ./db2topg.pl line 1021.
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1027.
syntax error at ./db2topg.pl line 1033, near "/)
"
(Might be a runaway multi-line "" string starting on line 1027)
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1034.
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1034.
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1037.
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1038.
Global symbol "$trigger" requires explicit package name at ./db2topg.pl line 1039.
syntax error at ./db2topg.pl line 1041, near "elsif"
./db2topg.pl has too many errors.

Hi Team,

im getting an table space error while run the ./db2topg.pl .

I don't understand FILE '/db2/database/aeibank/test/testdata' 333952, in a CREATE TABLESPACE section at ./db2topg.pl line 441, line 42.

i had exported the db2look ( db2look -d my_database_name -e -l -xd -o my_db2_sql_script) and copied the dump into my app server and ran the db2topg.pl . do i need to run the db2topg.pl in db2 server itself instead the non db2 server ?

Hi i got an issue where try to use db2topg.pl
I got error
./db2topg.pl -f my_db2_sql_script.sql -o ../output -d pempik -u db2inst1 -p aefuc3Co
I don't understand COLLECT SAMPLED DETAILED STATISTICS in an CREATE INDEX section at ./db2topg.pl line 893, line 252.
It is part of creating index:

CREATE INDEX "DB2INST1"."IDX1701240806260" ON "schema"."table" ("ID_LOYAL" ASC, "ID" ASC, "LP_REGISTRATION_DT" ASC) COLLECT SAMPLED DETAILED STATISTICS COMPRESS YES INCLUDE NULL KEYS ALLOW REVERSE SCANS;

these script support db2 in 10.5 version?

Hi Damien,

We are getting below error while running db2topg.pl to migrate our existing db2 schema to PostgreSQL server.
Error message:

I don't understand FILE '/A300202i/DLIINFO.f3' 52752) in a CREATE TABLESPACE section at ./db2topg.pl line 441, line 504.

Hi,

first of all thanks for this script!

While executing "./db2topg.pl -f db2_sql_script -o Folder -d XXX -p ***" I receive the following output: "iso-8859-15 or utf8 at ./db2topg.pl line 383"

Is this an error or just a information about the decoding output of guess_encoding?

The following Code snippet includes the relevant lines:

[382] my $decoder = guess_encoding($data_guess, qw/iso8859-15 utf8 utf16-le utf16-be/);
[383] die $decoder unless ref($decoder);

The problem is that I don't get any output file/folder nor info/warning from the above-named command.

Thank you for your help

Hello,

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 <CREATE TABLE "A90DVDT"."DLR_FAM_MRKTSHR_FY_END"

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)
);

We tried removing double quotes and all comments from file, still we are receiving same error.

Thanks,
Swapnil Vaze

Hello,
I am testing db2topg and hitting below error. Can please help me in this regard.

Error:
I don't understand <CREATE SUMMARY TABLE ADEFUSR AS (SELECT workdept, count(*) AS no_of_employees FROM employee GROUP BY workdept) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM IN "USERSPACE1" > at ./db2topg.pl line 1092, line 450.

Thanks
Rajni

Hi,

I am getting the below errors when I invoke the db2topg.pl

dwdev@hitdwdev01:dwdev:$ ./db2topg.pl -f my_db2_sql_script -o my_output_dir -d dwdev -u test -p test
./db2topg.pl[7]: syntax error at line 8 : newline or ;' unexpected dwdev@hitdwdev01:dwdev:$ dwdev@hitdwdev01:dwdev:$ dwdev@hitdwdev01:dwdev:$ dwdev@hitdwdev01:dwdev:$ dwdev@hitdwdev01:dwdev:$ dwdev@hitdwdev01:dwdev:$ ././db2topg.pl ././db2topg.pl[7]: syntax error at line 8 : newline or ;' unexpected
dwdev@hitdwdev01:dwdev:$ db2topg.pl
db2topg.pl[7]: syntax error at line 8 : `newline or ;' unexpected
dwdev@hitdwdev01:dwdev:$

Our server is an AIX box and any help on this is appreciated.

Regards,
K.Diwakar

hi
when i excute ./db2topg.pl -f my_db2_sql_script -o my_output_dir -d db2_dbname -u db2_user -p db2_pwd i have this error :

linea 1: error sintáctico cerca del elemento inesperado `newline'

línea 1: `!DOCTYPE html'

any idea?

Thanks

Hi,

When I execute the db2topg.pl file for converting my DB2 DDL to PostgreSQL DB, I am getting the following error.

./db2topg.pl
./db2topg.pl: line 7: syntax error near unexpected token newline' ./db2topg.pl: line 7: '

Also when I tried an alternate way I got this,

perl db2topg.pl -f ddl.query -o test -d inach -u achinst1 -p Achuat@db
Bareword found where operator expected at db2topg.pl line 23, near "<title>db2topg"
(Missing operator before db2topg?)
syntax error at db2topg.pl line 10, near "<"
Unrecognized character \xC2 in column 39 at db2topg.pl line 23.

Help me out.

Getting the following error:-

technology@technology-EPS:~/Downloads/db2topg-master$ ./db2topg.pl -f /home/technology/Downloads/bqeps_l.sql -o db_migration -d bqeps_l -u db2admin -p admin@123
I don't understand <CREATE SEQUENCE "EPS"."ACTION_TYPE_MAIL_MASTER" AS INTEGER START WITH 100 INCREMENT BY 1 MINVALUE 100 MAXVALUE 2147483647 NO CYCLE NO CACHE ORDER> at ./db2topg.pl line 934, line 3.

Please suggest.

ADMIN.MSTJCNODELOCKPI is clustered.Clustered (or IOT) indexes aren't supported in PostgreSQL. Creating a normal index
I don't understand <create function db2ext.textSearch ( pTextSearchArg VARCHAR(4096), pIndexCreator> at ./db2topg.pl line 1084, line 17824.

We tried to run the sql file with db2topg.pl and getting some error while processing . I have run the command with ./db2topg.pl -f my_db2_sql_script -o my_output_dir -d db2_dbname -u db2_user -p db2_pwd

Getting error like
I don't understand < IF (D_REVISION IS NULL AND CP_REVISION IS NULL AND CN_REVISION IS NULL) THEN> at ./db2topg.pl line 1119, <IN> line 1828.
Any suggestions ?

I tried to migrate the DB2 to Postgresql, at the time it shows error as Cannot open /home/db2topgdir/TABLEDESC, No such file or directory at ./deltocopy.pl line 234 while trying execute ./deltocopy.pl -d /home/db2topgdir/ | psql -U postgres -e --set=ON_ERROR_STOP=1 RBD

Hi Team,

I am using db2topg on Windows server. i am running these commands on cmd by connecting to db2. I ran the first command and it worked perfectly fine.
db2look -d DB_Name -e -l -xd -o file_name -i user_name -w pass

When i ran second command on same it is giving error.
./db2topg.pl -f my_db2_sql_script -o my_output_dir -d db2_dbname -u db2_user -p db2_pwd

'.' is not recognized as an internal or external command,
operable program or batch file.

I tried below given command as well and it also gave error.
db2 ./db2topg.pl -f my_db2_sql_script -o my_output_dir -d db2_dbname -u db2_user -p db2_pwd

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "./" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include: "". SQLSTATE=42601. Could anyone suggest on this. Thanks, Aayush

DB2 has various string units (https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_stringunitspec.html)

And an extracted DDL for that column can contain that units.
e.g. "HH" VARCHAR(4 OCTETS),

This causes a table creation command for postgresql to be

hh VARCHAR(4 OCTETS),

this is not understood by postgres create table command..

Thank you.

My solution is check quoted when $field eq ''

		if ($field eq '') # fast exit for empty (should mean null)
		{
			if ($colnull[$num] == 1)
			{
                                    my $quoted = $csv->is_qoted($num);   # keep empty string
				$field = $quoted ? '' : '\N';
			}
			# No need to set it at '' if null isn't authorized, it is already
			$outrow.= $field;
			next;
		}

I tried to use db2topg script to convert Db2 code to Postgres database and getting this kind of error for constraints,ALTER TABLE and CREATE TABLE statements. Please confirm if this script will work for all type of objects type.

[db2inst1@OESdb2pocserver db2topg-master]$ vi /u01/db2inst1/my_db2_sql_script.sql [db2inst1@OESdb2pocserver db2topg-master]$ ./db2topg.pl -f /u01/db2inst1/my_db2_sql_script.sql -o /u01/db2inst1/output_dir/ -d TESTDB -u adminuser -p ******
I don't understand ALTER TABLE "DB2INST1"."EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPNO") ENFORCED in an ALTER TABLE section at ./db2topg.pl line 829, line 202.