[
https://issues.apache.org/jira/browse/SQOOP-1823?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14231962#comment-14231962
]
Abraham Elmahrek commented on SQOOP-1823:
-----------------------------------------
[~jarcec], here are a few commands that have helped me:
{code:type=SQL|title="Fetch foreign key column references"}
SELECT C.CONSTRAINTNAME,T.TABLENAME,M.DESCRIPTOR FROM SYS.SYSCONSTRAINTS C JOIN
SYS.SYSFOREIGNKEYS F ON F.CONSTRAINTID=C.CONSTRAINTID JOIN SYS.SYSCONGLOMERATES
M ON M.CONGLOMERATEID=F.CONGLOMERATEID JOIN SYS.SYSTABLES T ON
T.TABLEID=C.TABLEID WHERE TYPE='F';
{code}
The above query will fetch foreign key column references. The references are
serialized POJOs with numeric references to column numbers. Use the query below
to lookup the column numbers.
{code:type=SQL|title="Fetch a column by column number and table name"}
SELECT T.TABLENAME, COLS.COLUMNNAME FROM SYS.SYSCOLUMNS COLS JOIN SYS.SYSTABLES
T ON T.TABLEID=COLS.REFERENCEID WHERE COLS.COLUMNNUMBER=2 AND
T.TABLENAME='SQ_CONNECTOR_DIRECTIONS';
{code}
A similar query is possible to retrieve the columns from which the constraint
is sourced from:
{code:type=SQL|title="Fetch a constraint columns"}
SELECT C.CONSTRAINTNAME,T.TABLENAME,M.DESCRIPTOR FROM SYS.SYSCONSTRAINTS C JOIN
SYS.SYSFOREIGNKEYS F ON F.CONSTRAINTID=C.CONSTRAINTID JOIN SYS.SYSKEYS K ON
K.CONSTRAINTID=F.KEYCONSTRAINTID JOIN SYS.SYSCONGLOMERATES M ON
M.CONGLOMERATEID=K.CONGLOMERATEID JOIN SYS.SYSTABLES T ON T.TABLEID=C.TABLEID
WHERE TYPE='F';
{code}
Though, I believe the above SQL is cut off in "IJ".
> Sqoop2: Fix Derby schema documenation
> -------------------------------------
>
> Key: SQOOP-1823
> URL: https://issues.apache.org/jira/browse/SQOOP-1823
> Project: Sqoop
> Issue Type: Bug
> Components: sqoop2-derby-repository
> Affects Versions: 1.99.4
> Reporter: Abraham Elmahrek
> Assignee: Abraham Elmahrek
> Fix For: 1.99.5
>
> Attachments: SQOOP-1823.0.patch
>
>
> There are a few places where there foreign keys are inaccurately listed. For
> example:
> {noformat}
> * +------------------------------+
> * | SQ_CONNECTOR_DIRECTIONS |
> * +------------------------------+
> * | SQCD_ID: BIGINT PK AUTO-GEN |
> * | SQCD_CONNECTOR: BIGINT |FK SQCD_CONNECTOR(SQC_ID)
> * | SQCD_DIRECTION: BIGINT |FK SQCD_DIRECTION(SQD_ID)
> * +------------------------------+
> {noformat}
> In the above snippet, FK SQCD_CONNECTOR(SQC_ID) should be FK
> _SQ_CONFIGURABLE(SQC_ID).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
