Query to display participants in a referential constraint

Hi Folks. Ab out 10 years ago I posted this question: In dbaccess, when I display information about a table, I can get information about constraints from the Info menu. This shows me the name of the constraint, the referencing table and column as well as the referenced (PK) column(s). My question was: How can I get this in a query? At the time someone kindly responded that the info i seek in an undocumented catalog (or in sysmaster?). That answer is lost to the past (although I though I had saved it.) What I do have is this much-simplified query: select t.tabname[1,20] referenced, t2.tabname[1,20] referencer, c.constrname[1,20] constraint from systables t, sysreferences r, sysconstraints c, systables t2 where r.ptabid = t.tabid and c.tabid = t2.tabid and r.constrid = c.constrid and t.tabname = '%MASTER%' -- Parametrizable. order by 1, 2 ; But the other information I need is missing, of course. Why do I need this when dbaccess/info tells me what I need? Because the dbaccess-generated info can't be easily included in a report or script. Anyone know the insider stuff I need? Thanks! ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Jacob: Here is the query that myschema uses, with an example: > SELECT st.tabname, st.owner, rt.tabname as ref_tabname, rt.owner as ref_owner, sr.primary, sr.ptabid, sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner, si.idxname, si.tabid, si.part1, si.part2, si.part3, si.part4, si.part5, si.part6, si.part7, si.pa> rt8, si.part9, si.part10, si.part11, si.part12, si.part13,si.part14, si.part15, si.part16, rc.tabid, os.state, os2.state> FROM "informix".systables st, "informix".sysconstraints sc, "informix".sysindexe> s si, "informix".sysreferences sr, "informix".systables rt, "informix".sys> constraints rc, "informix".sysobjstate os, "informix".sysobjstate> os2 WHERE st.tabid = sc.tabid AND st.tabtype != 'Q' AND st.tabname NOT > MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*' AND rt.tabid = sr.ptabid > AND sc.constrid = sr.constrid AND sc.tabid = si.tabid AND sc.idxname > = si.idxname AND sc.constrtype = 'R' AND st.tabname MATCHES 'hie> rarchy_l2' AND os.tabid = st.tabid AND os.name = sc.constrname AND os.objtype = 'C' AND os2.tabid = st.tabid AND os2.nam> e = si.idxname AND os2.objtype = 'I' AND sr.primary = rc.constrid ORDER BY si.tabid, sc.constrid ;> > > > > > > > > > > > > > > tabname hierarchy_l2 owner art ref_tabname hierarchy_l1 ref_owner art primary 187 ptabid 117 delrule R constrid 221 constrname r113_221 constrtype R owner art idxname p644_7233 tabid 113 part1 1 part2 0 part3 0 part4 0 part5 0 part6 0 part7 0 part8 0 part9 0 part10 0 part11 0 part12 0 part13 0 part14 0 part15 0 part16 0 tabid 117 state E state E 1 row(s) retrieved. To get the column names you would have to map the part<n> colno values to syscolumn for the referenced table: select colname from syscolumns where tabid = 117 and colno = 1;colname one 1 row(s) retrieved. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art. I saved that long ago. It is both TMI and TLI, the latter because it indeed lacks the column names I may shorten the number of part columns, since my environment uses a max of 4 key columns (AFAIK) but I do need the column names, which makes it kinda ugly. I'll see if I can edit it to an easier read. Many of those conditions are, say interesting. Like "where tabtype != Q" . SOme other condition got mangled with a ">" character. Like " AND st.tabname NOT > MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*' " But I'm still hoping that insider will come to my rescue here. :-) ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Sorry about the extra chcaracters. SELECT st.tabname, st.owner, rt.tabname, rt.owner, sr.primary, sr.ptabid, sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner, si.idxname, si.tabid, c1.colname as key_1, case si.part2 when 0 then "No match" else c2.colname end case as key_2, case si.part3 when 0 then "No match" else c3.colname end case as key_3,rc.tabid, os.state, os2.state FROM "informix".systables st, "informix".sysconstraints sc, "informix".sysindexes si, "informix".sysreferences sr, "informix".systables rt, "informix".sysconstraints rc, "informix".sysobjstate os, "informix".sysobjstate os2, "informix".syscolumns c1, outer "informix".syscolumns c2, outer "informix".syscolumns c3 WHERE st.tabid = sc.tabid AND st.tabtype != 'Q' AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*' AND rt.tabid = sr.ptabid AND sc.constrid = sr.constrid AND sc.tabid = si.tabid AND sc.idxname = si.idxname AND sc.constrtype = 'R' AND st.tabname MATCHES 'hierarchy_l2' -- Here is the target tabname AND os.tabid = st.tabid AND os.name = sc.constrname AND os.objtype = 'C' AND os2.tabid = st.tabid AND os2.name = si.idxname AND os2.objtype = 'I' AND sr.primary = rc.constrid AND c1.tabid = rt.tabid AND c2.tabid = rt.tabid AND c3.tabid = rt.tabid AND c1.colno = si.part1 AND c2.colno = si.part2 AND c3.colno = si.part3 ORDER BY si.tabid, sc.constrid ; tabname hierarchy_l2 owner art tabname hierarchy_l1 owner art primary 187 ptabid 117 delrule R constrid 221 constrname r113_221 constrtype R owner art idxname p644_7233 tabid 113 key_1 one key_2 No match key_3 No match tabid 117 state E state E Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

I have found the SQL I pieced together 10+ years ago. I have modified it, commenting out what I consider to be TMI but left it in there for debugging. Notice the %MASTER% fort the table name. This is because I embedded it in a shell script as a parameter to the SQL. select pt.tabname pk_table, pc.idxname pk_index, pc.constrname pk_constraint, --pc.constrtype, --pc.constrid, ft.tabname fk_table, fc.idxname fk_index, fc.constrname fk_constraint --fc.constrtype, --fc.constrid from systables pt, systables ft, sysconstraints pc, sysconstraints fc, sysreferences r where pt.tabid = pc.tabid and pc.constrtype = 'P' --and pc.constrtype in ('P', 'R') -- TMI and ft.tabid = fc.tabid and fc.constrtype = 'R' and r.constrid = fc.constrid and r.ptabid = pt.tabid and pt.tabname in ("%MASTERS%") order by pt.tabname, ft.tabname, pc.constrname, fc.constrname ; If I want to add the column names as well it will get too ugly and unwieldy. I may still do that but I still wish that insider would come back with that old, undocumented information. But for now I'm satisfied. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

I doubt there's any undocumented insider secret to be told beyond what's already been said, and certainly not anything dbaccess knows but not the rest of us. If you run dbaccess through those constraint menu options and then look at its sqlidebug output, you'll be surprised how simple those queries are - there's not the slightest attempt of any complexity or multi-table queries, just very basic prepared single-table queries, some executed multiple times in a row. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Andreas, I am assuming you mean to say that if I set SQLIDEBUG correctly (no easy task, I now see) then when I use dbaccess/Query/Constraints/Reference/referenced, and then exit dbacces, I would see the SQL it used to produce its information. Let's put that to the test: Firstly: Although I have quite a collection of release 12.1 manuals (in PDF) I could not find information on how to use the SQLIDEBUG env variable. A Google search turned up this tidbit: SQLIDEBUG: Specifies the path name for the file to which a binary SQLI trace is to be written. A new trace file is generated for every connection and is suffixed with timestamp. Only use the SQLI trace facility when directed by the IBM technical support representative. I took this at its word: Attempt 1: export SQLIDEBUG=/usr/informix/scratch/mydbinfo.txt but when I started dbacess it tells me "Invalid syntax for SQLIDEBUG environment variable. " Attemp 2:Following an example I found on line I tried : export SQLIDEBUG=2:/usr/informix/scratch/mydbinfo.txt No error message here but I got a very big binary file, quite unreadable to human eyes. Attempt 3: On a long shot I tries the prefix 1 instead of 2: This got me an ASCII dump, no SELECT statements in sight. So how am I supposed to use SQLIDEBUG to make the discovery I seek? Thanks for the starting point,however. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

Jacob, I found the following by googling. Hope this helps. Been a few years since I have done Informix. How to Use SQLIDEBUG 1. Set as an Environment Variable (CLI / Client Environment) Before launching your client application or tool, define SQLIDEBUG in your operating system shell to point to a target output file path. [ 1 ] On Linux / UNIX: bash export SQLIDEBUG=/tmp/ifxtrace On Windows: cmd set SQLIDEBUG=C:\\tmp\\ifxtrace Behavior: A new trace file is generated for every individual connection, automatically suffixed with a unique timestamp (e.g., /tmp/ifxtrace_2026-09-14_... ). [ 1 , 2 ] 2. Set via JDBC Connection Property If you are using a Java program with the Informix JDBC driver, you can configure the trace file programmatically via IfxDataSource : [ 1 ] java IfxDataSource ds = new IfxDataSource(); ds.setIfxSQLIDEBUG( "/tmp/ifx_jdbc_trace" ); // Set other connection properties like URL, user, password... Connection conn = ds.getConnection(); Decoding the Trace File The output file generated by SQLIDEBUG is a binary protocol trace ( SQLI protocol format), meaning you cannot read it directly with a standard text editor. To interpret the contents of the trace file, you typically need to process it using the companion utility sqliprint (if available in your Informix Client SDK installation): [ 1 , 2 ] bash sqliprint /tmp/ifxtrace_timestamp > /tmp/readable_trace.out

Hi David. OK, I tried this, producing the sqlidebug binary file and feeding it though sqliprint into an flat text file. All I did in dbaccess was get information on the detail tables of one given table. The text file is over 14,000 lines long. Yes, there are a few SELECT statements in there but the excelsior is ridiculous! Here's a sample, with line numbers from vi so you can maybe see through the line wrapping: 1 SQLIDBG Version 1 2 3 S->C (4) Time: 2026-09-14 15:29:35.85543 4 SQ_INTERNALVER 5 Internal Version Number: 316 6 7 C->S (16) Time: 2026-09-14 15:29:35.85546 8 SQ_PROTOCOLS 9 Cprotocol stream len = 9 10 byte[0] = ff 11 byte[1] = fe 12 byte[2] = df 13 byte[3] = fe 14 byte[4] = 74 15 byte[5] = aa 16 byte[6] = 62 17 byte[7] = 13 18 byte[8] = c0 19 SQ_EOT 20 21 S->C (16) Time: 2026-09-14 15:29:35.87113 22 SQ_PROTOCOLS 23 Sprotocol stream len = 9 24 byte[0] = bd 25 byte[1] = be 26 byte[2] = 9f 27 byte[3] = fe 28 byte[4] = 7f 29 byte[5] = b7 30 byte[6] = ff 31 byte[7] = ef 32 byte[8] = f8 33 SQ_EOT 34 35 C->S (378) Time: 2026-09-14 15:29:35.87127 36 SQ_INFO 37 INFO_ENV 38 Name Length = 12 39 Value Length = 256 40 "DBTEMP"="/tmp" 41 "SHELL"="/bin/ksh" 42 "TZ"="localtime" 43 "SUBQCACHESZ"="10" 44 "DBPATH"="//misdev" 45 "PATH"="/opt/jdk11/bin:/usr/local/bin:/usr/bin:/bin:/usr/openwin/bin:/usr/local/bhdev/bin:/etc:/users/jsalomon/scripts:.:/usr/informix/bin:/apps1/informix7.51.FC1X2/lib/esql:/usr/informix:/usr/informix/bnh_ifmx_d ba:/batch:/usr/sbin:/usr/local/bin:/opt/ids/sdk/bin" 46 "NODEFDAC"="no" 47 INFO_DONE 48 SQ_EOT 49 50 S->C (2) Time: 2026-09-14 15:29:35.87140 51 SQ_EOT 52 53 C->S (8) Time: 2026-09-14 15:29:35.87140 54 SQ_CONNECT 55 56 C->S (28) Time: 2026-09-14 15:29:35.87142 57 SQ_PREPARE 58 # values: 0 59 CMD.....: "Database ' XXX '" [14] 60 SQ_NDESCRIBE 61 SQ_WANTDONE 62 SQ_EOT 63 ... YIKES! Readable, maybe, but not much more useful than the binary. But you did give me the idea to use SET EXPLAIN ON. That is quite a bit more readable. And only 138 lines, as opposed to 14,000. And I can see SELECT statements so I may be able to piece together the needed sequence, if not too ugly. Depending on how far I take this, I may be able to present something useful to the community. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

I'd recommend grepping from "CMD" as a start which will give you all the SQL sent to the server. Some of it might get executed directly, others prepared and then executed <n> times. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

this still exists onmode -p 1 sqli_dbgthen sqliprint a file sqliprint sqli.1781 SQLIDBG Version 1 S->C (4) Time: 2026-09-17 22:16:00.54063 SQ_INTERNALVER Internal Version Number: 316 C->S (16) Time: 2026-09-17 22:16:00.54092 SQ_PROTOCOLS Cprotocol stream len = 9 byte[0] = ff byte[1] = fe byte[2] = df byte[3] = fe byte[4] = 74 byte[5] = aa byte[6] = 62 byte[7] = 13 byte[8] = c0 SQ_EOT S->C (16) Time: 2026-09-17 22:16:00.54092 SQ_PROTOCOLS Sprotocol stream len = 9 byte[0] = bd byte[1] = be byte[2] = 9f byte[3] = fe byte[4] = 7f byte[5] = b7 byte[6] = ff byte[7] = ef byte[8] = f9 SQ_EOT C->S (186) Time: 2026-09-17 22:16:00.54116 SQ_INFO INFO_ENV Name Length = 12 Value Length = 84 "DBTEMP"="/tmp" "SHELL"="/bin/bash" "SUBQCACHESZ"="10" "DBPATH"="." "PATH"="/home/informix/live/bin:/home/paul/.local/bin:/home/paul/bin:/usr/local/bin:/usr/bin" "NODEFDAC"="no" INFO_DONE SQ_EOT S->C (2) Time: 2026-09-17 22:16:00.54119 SQ_EOT

That's certainly helpful, Paul. Two issues I have, however: If you turn on sqli_dbg this way, doesn't it operate on every blessed SQL running on the server? As opposed to the quickie session I am about to run? And I suppose you would have to run onmode -p -1 sqli_dbg to turn it off. This VP option is not mentioned in the 12.10 Admin Reference. In your example, you knew it produced file sqli.1781. In which directory is that? And mainly how did you know that numeric suffix? I have yet to try it (hey, I'm being paid for something around here! :-) but I think David's and Andreas's suggestions for setting of the env variable SQLIDEBUG locally makes a bit more sense for me. And then grep the output for the string CMD. Still, if you could please answer those points I raised, there may be need for this as well, though I ain't gonna do this on a live server with 3000+ active sessions! Thanks all so far! I will let y'all know the end result of this. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------

This is doc'd in one the developer guide, IBM Informix Developer's Handbook - Redbook SG24-7884 and Tomáš Zahradník / IBM Advanced Technical Support CIDUG presentation The default is /tmp/sqli, if that doesn't exist it lands in $INFORMXDIR/tmp. The latter exists in my world. No idea where it would try next. I just picked a number as an example, it is the session id This is not something I would run in prod. Note onmode -p-1 sqli_dbg only appears to deactivate the capture, the VP is still there and still clocks CPU, although very little.