Hi,
I'am try using CHECK_FOREIGN_KEY with below configuration, but my output file is empty! Could someone help me?
PS: I've to change this properties:
Data.DATA_RECORDS_BUFFER_SIZE= 10485760 -> 104857600
DataFormatter.FIELD_BUFFER_LENGTH= 512 -> 1024
DataParser.FIELD_BUFFER_LENGTH= 512 -> 1024
Thanks!
My graph
--------
<?xml version="1.0" encoding="UTF-8"?>
<Graph description="" name="Local">
<Global>
<Metadata fileURL="metadata\Local.fmt" id="Metadata0"/>
<Metadata fileURL="metadata\ROP_TPO_LOCAL.fmt" id="Metadata1"/>
<DBConnection dbConfig="metadata\ROP.cfg" id="DBConnection0"/>
</Global>
<Phase number="0">
<Node defaultForeignKey="0;0" foreignKey="CdTipoLoc;SgEmp" guiHeight="0" guiName="Check Foreign Key" guiWidth="0" guiX="202" guiY="32" id="CHECK_FOREIGN_KEY0" primaryKey="TpLocal;SgEmpresa" type="CHECK_FOREIGN_KEY"/>
<Node dbConnection="DBConnection0" guiHeight="0" guiName="DB Input Table" guiWidth="0" guiX="19" guiY="32" id="DB_INPUT_TABLE0" type="DB_INPUT_TABLE">
<attr name="SQLCode">select CD_EMPRESA, CD_TIPO_LOCAL, NM_TIPO_LOCAL from ROP.ROP_TPO_LOCAL</attr>
</Node>
<Node filterExpression="$CdTipoLoc>0 and $SgEmp>0" guiHeight="0" guiName="Ext Filter" guiWidth="0" guiX="197" guiY="124" id="EXT_FILTER0" type="EXT_FILTER"/>
<Node OneRecordPerLine="true" fileURL="input\Local.txt" guiHeight="0" guiName="Fixed Length Data Reader (New I/O)" guiWidth="0" guiX="21" guiY="142" id="FIXLEN_DATA_READER_NIO0" type="FIXLEN_DATA_READER_NIO"/>
<Node OneRecordPerLine="true" append="false" fileURL="ouput\ok.txt" guiHeight="0" guiName="Fixed Length Data Writer (New I/O)" guiWidth="0" guiX="377" guiY="124" id="FIXLEN_DATA_WRITER_NIO0" type="FIXLEN_DATA_WRITER_NIO"/>
<Node OneRecordPerLine="true" append="false" fileURL="ouput\not_ok.txt" guiHeight="0" guiName="Fixed Length Data Writer (New I/O)" guiWidth="0" guiX="377" guiY="237" id="FIXLEN_DATA_WRITER_NIO1" type="FIXLEN_DATA_WRITER_NIO"/>
<Edge fromNode="CHECK_FOREIGN_KEY0:0" guiBendpoints="" id="Edge0" metadata="Metadata0" toNode="EXT_FILTER0:0"/>
<Edge fromNode="DB_INPUT_TABLE0:0" guiBendpoints="" id="Edge3" metadata="Metadata1" toNode="CHECK_FOREIGN_KEY0:0"/>
<Edge fromNode="EXT_FILTER0:0" guiBendpoints="" id="Edge1" metadata="Metadata0" toNode="FIXLEN_DATA_WRITER_NIO0:0"/>
<Edge fromNode="EXT_FILTER0:1" guiBendpoints="" id="Edge2" metadata="Metadata0" toNode="FIXLEN_DATA_WRITER_NIO1:0"/>
<Edge fromNode="FIXLEN_DATA_READER_NIO0:0" guiBendpoints="" id="Edge4" metadata="Metadata0" toNode="CHECK_FOREIGN_KEY0:1"/>
</Phase>
</Graph>
metadata\Local.fmt
------------------
<?xml version="1.0" encoding="UTF-8"?>
<Record name="Local" type="fixed">
<Field name="SgEmp" nullable="true" size="15" type="string"/>
<Field name="CdTipoLoc" nullable="true" size="2" type="integer"/>
<Field name="CdLoc" nullable="true" size="6" type="long"/>
<Field name="IdHierLoc" nullable="true" size="2" type="string"/>
<Field name="NmLoc" nullable="true" size="30" type="string"/>
<Field name="CdAg" nullable="true" size="4" type="integer"/>
<Field name="NmEndAbrev" nullable="true" size="25" type="string"/>
<Field name="NmRuaLoc" nullable="true" size="40" type="string"/>
<Field name="NoPredLoc" nullable="true" size="5" type="long"/>
<Field name="DeComplEndLoc" nullable="true" size="20" type="string"/>
<Field name="NmBairroLoc" nullable="true" size="10" type="string"/>
<Field name="NmBairro" nullable="true" size="25" type="string"/>
<Field name="NoCepLoc" nullable="true" size="5" type="long"/>
<Field name="NoComplCep" nullable="true" size="3" type="integer"/>
<Field name="SgEstLoc" nullable="true" size="2" type="string"/>
<Field name="NmCidLoc" nullable="true" size="15" type="string"/>
<Field name="NmCid" nullable="true" size="38" type="string"/>
<Field name="CdPcaLoc" nullable="true" size="3" type="integer"/>
<Field name="CdPcaBb" nullable="true" size="6" type="long"/>
<Field name="CdDdiLoc" nullable="true" size="5" type="long"/>
<Field name="CdDddLoc" nullable="true" size="5" type="long"/>
<Field name="NoTelLoc" nullable="true" size="8" type="long"/>
<Field name="NoRmLoc" nullable="true" size="4" type="integer"/>
<Field name="NoCvLoc" nullable="true" size="4" type="integer"/>
<Field name="CdContr" nullable="true" size="9" type="long"/>
<Field name="NmRespLoc" nullable="true" size="30" type="string"/>
<Field name="DcAberLoc" nullable="true" size="8" type="string"/>
<Field name="DcEnceLoc" nullable="true" size="8" type="string"/>
<Field name="DtAberDisque" nullable="true" size="8" type="string"/>
<Field name="DtAltEndLoc" nullable="true" size="8" type="string"/>
<Field name="CdAgSubst" nullable="true" size="4" type="integer"/>
<Field name="NoCgcLoc" nullable="true" size="15" type="long"/>
<Field name="NoCcmLoc" nullable="true" size="15" type="string"/>
<Field name="NoInscrEstlLoc" nullable="true" size="12" type="string"/>
<Field name="CdAlfaPostoServ" nullable="true" size="1" type="string"/>
<Field name="NoCgcEmpPab" nullable="true" size="15" type="long"/>
<Field name="InAprovPabBacen" nullable="true" size="1" type="string"/>
<Field name="CdFusoHora" nullable="true" size="1" type="string"/>
<Field name="NmDirtrSegmRespAutrz" nullable="true" size="40" type="string"/>
<Field name="CdCentroCusto" nullable="true" size="5" type="long"/>
<Field name="CdDddFax" nullable="true" size="5" type="long"/>
<Field name="NoFax" nullable="true" size="8" type="long"/>
<Field name="SgDepto" nullable="true" size="8" type="string"/>
<Field name="CdLocUnico" nullable="true" size="9" type="long"/>
<Field name="CdBco" nullable="true" size="3" type="integer"/>
<Field name="CdCorreioEletr" nullable="true" size="8" type="string"/>
<Field name="NmEmail" nullable="true" size="50" type="string"/>
<Field name="CdFinaldLoc" nullable="true" size="2" type="integer"/>
<Field name="CdCaractLoc" nullable="true" size="2" type="integer"/>
<Field name="CdAtivBr" nullable="true" size="2" type="integer"/>
<Field name="CdNivRat" nullable="true" size="2" type="integer"/>
<Field name="NoAutBacen" nullable="true" size="10" type="long"/>
<Field name="CdRespLoc" nullable="true" size="4" type="integer"/>
<Field name="CdRespLocSubst" nullable="true" size="4" type="integer"/>
<Field name="DeMotivoEnce" nullable="true" size="20" type="string"/>
<Field name="CdPais" nullable="true" size="3" type="integer"/>
<Field name="Filler" nullable="true" size="698" type="string"/>
</Record>
metadata\ROP_TPO_LOCAL.fmt
--------------------------
<?xml version="1.0" encoding="UTF-8"?>
<Record name="defaultRecordName" type="delimited">
<Field delimiter=";" name="SgEmpresa" nullable="true" type="string"/>
<Field delimiter=";" name="TpLocal" nullable="true" type="numeric"/>
<Field delimiter="\n" name="DsTpLocal" nullable="true" type="string"/>
</Record>
My table
--------
create table TEMP_ROP_LOCAL(
CD_LOCAL NUMBER(6),
NM_LOCAL VARCHAR2(30),
SG_LOCAL VARCHAR2(2),
CD_AGENCIA NUMBER(4),
DT_ABERTURA DATE,
DT_ABERTURA_PREVISTA DATE,
DT_ENCERRAMENTO DATE,
CD_TIPO_LOCAL NUMBER(2),
DT_ULTIMA_ALTERACAO DATE,
CD_AGENCIA_SUBSTITUTA NUMBER(4),
CD_LOCAL_UNICO NUMBER(9) PRIMARY KEY not null,
CD_EMPRESA VARCHAR2(15),
DT_LOCAL_ATUAL DATE)
My input file (Local.txt - original file has 27MB)
--------------------------------------------------
AABTP 00000197LOXXX XPTO BRASIL TRES PARTIC. 0000 AVENIDA PAULISTA 013743 ANDAR, PARTE BELA VISTABELA VISTA 01310916SPSAO PAULO SAO PAULO 28000738800000000000000000000000000000000000 200506240000000020050624200507010000007152467000104 000000000000000N0 000000000000000000 000265541000 90999902000000000000000000 000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
AAP 00005100LOAGRIPAR-ADMINIS.PARTIC.LTDA 0000 AV. PAULISTA 013743 ANDAR BELA VISTABELA VISTA 01310916SPSAO PAULO SAO PAULO 28000738800000000000000000000000000000000000 199211042000011100000000200507250000050590140000135 000000000000000N0 000000000000000000 000065452000 90999902000000000000000000 000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
My output file (not_ok.txt - rejected records)
----------------------------------------------
0 0 197 LOXXX XPTO BRASIL TRES PARTIC. 0 AVENIDA PAULISTA 1374 3 ANDAR, PARTE BELA VISTABELA VISTA 1310 916SPSAO PAULO SAO PAULO 2807388 0 0 0 0 0 0 200506240000000020050624200507010 7152467000104 0 N0 0 0 0 265541 0 9099992 0 0 0 0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
0 0 5100 LOAGRIPAR-ADMINIS.PARTIC.LTDA 0 AV. PAULISTA 1374 3 ANDAR BELA VISTABELA VISTA 1310 916SPSAO PAULO SAO PAULO 2807388 0 0 0 0 0 0 199211042000011100000000200507250 50590140000135 0 N0 0 0 0 65452 0 9099992 0 0 0 0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
I'am try using CHECK_FOREIGN_KEY with below configuration, but my output file is empty! Could someone help me?
PS: I've to change this properties:
Data.DATA_RECORDS_BUFFER_SIZE= 10485760 -> 104857600
DataFormatter.FIELD_BUFFER_LENGTH= 512 -> 1024
DataParser.FIELD_BUFFER_LENGTH= 512 -> 1024
Thanks!
My graph
--------
<?xml version="1.0" encoding="UTF-8"?>
<Graph description="" name="Local">
<Global>
<Metadata fileURL="metadata\Local.fmt" id="Metadata0"/>
<Metadata fileURL="metadata\ROP_TPO_LOCAL.fmt" id="Metadata1"/>
<DBConnection dbConfig="metadata\ROP.cfg" id="DBConnection0"/>
</Global>
<Phase number="0">
<Node defaultForeignKey="0;0" foreignKey="CdTipoLoc;SgEmp" guiHeight="0" guiName="Check Foreign Key" guiWidth="0" guiX="202" guiY="32" id="CHECK_FOREIGN_KEY0" primaryKey="TpLocal;SgEmpresa" type="CHECK_FOREIGN_KEY"/>
<Node dbConnection="DBConnection0" guiHeight="0" guiName="DB Input Table" guiWidth="0" guiX="19" guiY="32" id="DB_INPUT_TABLE0" type="DB_INPUT_TABLE">
<attr name="SQLCode">select CD_EMPRESA, CD_TIPO_LOCAL, NM_TIPO_LOCAL from ROP.ROP_TPO_LOCAL</attr>
</Node>
<Node filterExpression="$CdTipoLoc>0 and $SgEmp>0" guiHeight="0" guiName="Ext Filter" guiWidth="0" guiX="197" guiY="124" id="EXT_FILTER0" type="EXT_FILTER"/>
<Node OneRecordPerLine="true" fileURL="input\Local.txt" guiHeight="0" guiName="Fixed Length Data Reader (New I/O)" guiWidth="0" guiX="21" guiY="142" id="FIXLEN_DATA_READER_NIO0" type="FIXLEN_DATA_READER_NIO"/>
<Node OneRecordPerLine="true" append="false" fileURL="ouput\ok.txt" guiHeight="0" guiName="Fixed Length Data Writer (New I/O)" guiWidth="0" guiX="377" guiY="124" id="FIXLEN_DATA_WRITER_NIO0" type="FIXLEN_DATA_WRITER_NIO"/>
<Node OneRecordPerLine="true" append="false" fileURL="ouput\not_ok.txt" guiHeight="0" guiName="Fixed Length Data Writer (New I/O)" guiWidth="0" guiX="377" guiY="237" id="FIXLEN_DATA_WRITER_NIO1" type="FIXLEN_DATA_WRITER_NIO"/>
<Edge fromNode="CHECK_FOREIGN_KEY0:0" guiBendpoints="" id="Edge0" metadata="Metadata0" toNode="EXT_FILTER0:0"/>
<Edge fromNode="DB_INPUT_TABLE0:0" guiBendpoints="" id="Edge3" metadata="Metadata1" toNode="CHECK_FOREIGN_KEY0:0"/>
<Edge fromNode="EXT_FILTER0:0" guiBendpoints="" id="Edge1" metadata="Metadata0" toNode="FIXLEN_DATA_WRITER_NIO0:0"/>
<Edge fromNode="EXT_FILTER0:1" guiBendpoints="" id="Edge2" metadata="Metadata0" toNode="FIXLEN_DATA_WRITER_NIO1:0"/>
<Edge fromNode="FIXLEN_DATA_READER_NIO0:0" guiBendpoints="" id="Edge4" metadata="Metadata0" toNode="CHECK_FOREIGN_KEY0:1"/>
</Phase>
</Graph>
metadata\Local.fmt
------------------
<?xml version="1.0" encoding="UTF-8"?>
<Record name="Local" type="fixed">
<Field name="SgEmp" nullable="true" size="15" type="string"/>
<Field name="CdTipoLoc" nullable="true" size="2" type="integer"/>
<Field name="CdLoc" nullable="true" size="6" type="long"/>
<Field name="IdHierLoc" nullable="true" size="2" type="string"/>
<Field name="NmLoc" nullable="true" size="30" type="string"/>
<Field name="CdAg" nullable="true" size="4" type="integer"/>
<Field name="NmEndAbrev" nullable="true" size="25" type="string"/>
<Field name="NmRuaLoc" nullable="true" size="40" type="string"/>
<Field name="NoPredLoc" nullable="true" size="5" type="long"/>
<Field name="DeComplEndLoc" nullable="true" size="20" type="string"/>
<Field name="NmBairroLoc" nullable="true" size="10" type="string"/>
<Field name="NmBairro" nullable="true" size="25" type="string"/>
<Field name="NoCepLoc" nullable="true" size="5" type="long"/>
<Field name="NoComplCep" nullable="true" size="3" type="integer"/>
<Field name="SgEstLoc" nullable="true" size="2" type="string"/>
<Field name="NmCidLoc" nullable="true" size="15" type="string"/>
<Field name="NmCid" nullable="true" size="38" type="string"/>
<Field name="CdPcaLoc" nullable="true" size="3" type="integer"/>
<Field name="CdPcaBb" nullable="true" size="6" type="long"/>
<Field name="CdDdiLoc" nullable="true" size="5" type="long"/>
<Field name="CdDddLoc" nullable="true" size="5" type="long"/>
<Field name="NoTelLoc" nullable="true" size="8" type="long"/>
<Field name="NoRmLoc" nullable="true" size="4" type="integer"/>
<Field name="NoCvLoc" nullable="true" size="4" type="integer"/>
<Field name="CdContr" nullable="true" size="9" type="long"/>
<Field name="NmRespLoc" nullable="true" size="30" type="string"/>
<Field name="DcAberLoc" nullable="true" size="8" type="string"/>
<Field name="DcEnceLoc" nullable="true" size="8" type="string"/>
<Field name="DtAberDisque" nullable="true" size="8" type="string"/>
<Field name="DtAltEndLoc" nullable="true" size="8" type="string"/>
<Field name="CdAgSubst" nullable="true" size="4" type="integer"/>
<Field name="NoCgcLoc" nullable="true" size="15" type="long"/>
<Field name="NoCcmLoc" nullable="true" size="15" type="string"/>
<Field name="NoInscrEstlLoc" nullable="true" size="12" type="string"/>
<Field name="CdAlfaPostoServ" nullable="true" size="1" type="string"/>
<Field name="NoCgcEmpPab" nullable="true" size="15" type="long"/>
<Field name="InAprovPabBacen" nullable="true" size="1" type="string"/>
<Field name="CdFusoHora" nullable="true" size="1" type="string"/>
<Field name="NmDirtrSegmRespAutrz" nullable="true" size="40" type="string"/>
<Field name="CdCentroCusto" nullable="true" size="5" type="long"/>
<Field name="CdDddFax" nullable="true" size="5" type="long"/>
<Field name="NoFax" nullable="true" size="8" type="long"/>
<Field name="SgDepto" nullable="true" size="8" type="string"/>
<Field name="CdLocUnico" nullable="true" size="9" type="long"/>
<Field name="CdBco" nullable="true" size="3" type="integer"/>
<Field name="CdCorreioEletr" nullable="true" size="8" type="string"/>
<Field name="NmEmail" nullable="true" size="50" type="string"/>
<Field name="CdFinaldLoc" nullable="true" size="2" type="integer"/>
<Field name="CdCaractLoc" nullable="true" size="2" type="integer"/>
<Field name="CdAtivBr" nullable="true" size="2" type="integer"/>
<Field name="CdNivRat" nullable="true" size="2" type="integer"/>
<Field name="NoAutBacen" nullable="true" size="10" type="long"/>
<Field name="CdRespLoc" nullable="true" size="4" type="integer"/>
<Field name="CdRespLocSubst" nullable="true" size="4" type="integer"/>
<Field name="DeMotivoEnce" nullable="true" size="20" type="string"/>
<Field name="CdPais" nullable="true" size="3" type="integer"/>
<Field name="Filler" nullable="true" size="698" type="string"/>
</Record>
metadata\ROP_TPO_LOCAL.fmt
--------------------------
<?xml version="1.0" encoding="UTF-8"?>
<Record name="defaultRecordName" type="delimited">
<Field delimiter=";" name="SgEmpresa" nullable="true" type="string"/>
<Field delimiter=";" name="TpLocal" nullable="true" type="numeric"/>
<Field delimiter="\n" name="DsTpLocal" nullable="true" type="string"/>
</Record>
My table
--------
create table TEMP_ROP_LOCAL(
CD_LOCAL NUMBER(6),
NM_LOCAL VARCHAR2(30),
SG_LOCAL VARCHAR2(2),
CD_AGENCIA NUMBER(4),
DT_ABERTURA DATE,
DT_ABERTURA_PREVISTA DATE,
DT_ENCERRAMENTO DATE,
CD_TIPO_LOCAL NUMBER(2),
DT_ULTIMA_ALTERACAO DATE,
CD_AGENCIA_SUBSTITUTA NUMBER(4),
CD_LOCAL_UNICO NUMBER(9) PRIMARY KEY not null,
CD_EMPRESA VARCHAR2(15),
DT_LOCAL_ATUAL DATE)
My input file (Local.txt - original file has 27MB)
--------------------------------------------------
AABTP 00000197LOXXX XPTO BRASIL TRES PARTIC. 0000 AVENIDA PAULISTA 013743 ANDAR, PARTE BELA VISTABELA VISTA 01310916SPSAO PAULO SAO PAULO 28000738800000000000000000000000000000000000 200506240000000020050624200507010000007152467000104 000000000000000N0 000000000000000000 000265541000 90999902000000000000000000 000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
AAP 00005100LOAGRIPAR-ADMINIS.PARTIC.LTDA 0000 AV. PAULISTA 013743 ANDAR BELA VISTABELA VISTA 01310916SPSAO PAULO SAO PAULO 28000738800000000000000000000000000000000000 199211042000011100000000200507250000050590140000135 000000000000000N0 000000000000000000 000065452000 90999902000000000000000000 000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
My output file (not_ok.txt - rejected records)
----------------------------------------------
0 0 197 LOXXX XPTO BRASIL TRES PARTIC. 0 AVENIDA PAULISTA 1374 3 ANDAR, PARTE BELA VISTABELA VISTA 1310 916SPSAO PAULO SAO PAULO 2807388 0 0 0 0 0 0 200506240000000020050624200507010 7152467000104 0 N0 0 0 0 265541 0 9099992 0 0 0 0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
0 0 5100 LOAGRIPAR-ADMINIS.PARTIC.LTDA 0 AV. PAULISTA 1374 3 ANDAR BELA VISTABELA VISTA 1310 916SPSAO PAULO SAO PAULO 2807388 0 0 0 0 0 0 199211042000011100000000200507250 50590140000135 0 N0 0 0 0 65452 0 9099992 0 0 0 0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000S00000000
-
Hello !
Your graph seems to be OK. I can't really tell much since I am not the author of the component.
It may be a data related problem. Your "SgEmp" field from source (foreign) data has length 15 chars and it looks like it is padded to the full length, whereas your DB data is varchar which means not padded - that can be the problem - you may be comparing "AAA " versus "AAA" which is not the same.
David.
Please sign in to leave a comment.
Comments 1