QSYS2/SYSCOLUMNS missing

135 pts.
Tags:
AS/400
When using ACCESS or EXCEL queries via ODBC to AS400 files (V5R1) I get an error "SYSCOLUMNS is missing" How can I rebuild it? Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

have you checked the option to show system tables ?

====================================================

Verify that system value QSYSLIBL has library QSYS2 in the list. Add it if it’s missing.

Tom

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DeadMeatinLA
    I don't have access to nay V5R1 systems at the moment... but according the DSPFD info in V5R3... this is how mine was created: SQL create statement: CREATE VIEW SYSCOLUMNS (COLUMN_NAME FOR "NAME", TABLE_NAME FOR "TBNA ME", TABLE_OWNER FOR "TBCREATOR", ORDINAL_POSITION FOR "COLNO", DATA _TYPE FOR "COLTYPE", "LENGTH", NUMERIC_SCALE FOR "SCALE", IS_NULLABL E FOR "NULLS", IS_UPDATABLE FOR "UPDATES", LONG_COMMENT FOR "REMARKS ", HAS_DEFAULT FOR "DEFAULT", COLUMN_HEADING FOR "LABEL", "STORAGE", NUMERIC_PRECISION FOR "PRECISION", "CCSID", TABLE_SCHEMA FOR "DBNAME ", COLUMN_DEFAULT FOR "DFTVALUE", CHARACTER_MAXIMUM_LENGTH FOR "CHAR LEN", CHARACTER_OCTET_LENGTH FOR "CHARBYTE", NUMERIC_PRECISION_RADIX FOR "RADIX", DATETIME_PRECISION FOR "DATPRC", COLUMN_TEXT FOR "LABE LTEXT", SYSTEM_COLUMN_NAME FOR "SYS_CNAME", SYSTEM_TABLE_NAME FOR "S YS_TNAME", SYSTEM_TABLE_SCHEMA FOR "SYS_DNAME", USER_DEFINED_TYPE_SC HEMA FOR "TYPESCHEMA", USER_DEFINED_TYPE_NAME FOR "TYPENAME", IS_IDE NTITY FOR "IDENTITY", IDENTITY_GENERATION FOR "GENERATED", IDENTITY_ START FOR "START", IDENTITY_INCREMENT FOR "INCREMENT", IDENTITY_MINI MUM FOR "MINVALUE", IDENTITY_MAXIMUM FOR "MAXVALUE", IDENTITY_CYCLE FOR "CYCLE", IDENTITY_CACHE FOR "CACHE", IDENTITY_ORDER FOR "ORDER" ) AS SELECT DBILFL, DBILFI, VARCHAR(STRIP(DBIOWN),128), INTEGER(DBIP OS), CASE WHEN DBITYP = 'DISTINCT' AND DBITLN = 'ROWID' THEN 'ROWID' ELSE DBITYP END , DBIFLN, INTEGER(DBINSC), DBINUL, DBIUPD, DBIREM, DBIDFI, DBIHDG, DBIILN, INTEGER(DBINLN), DBICCC, DBILB2, DBIDFT, DBI CNC, DBICLN, INTEGER(DBIRDX), INTEGER(DBIDLN), DBITXT, DBIFLD, DBIFI L, DBILIB, DBITLB, DBITLN, CASE WHEN DBIDFI IN ('I', 'J') THEN 'YES' ELSE 'NO' END, CASE WHEN DBIDFI IN ('A', 'I') THEN 'ALWAYS' WHEN DB IDFI IN ('D','J') THEN 'BY DEFAULT' ELSE NULL END, DBXIDSTRTV, DBXID INC, DBXIDMINV, DBXIDMAXV, DBXIDCYCLE, DBXIDCACHE, DBXIDORDER FROM Q SYS.QADBIFLD LEFT OUTER JOIN QSYS.QADBXSFLD ON DBILIB=DBXSFLIB AND D BIFIL=DBXSFFILE AND DBIFLD=DBXSFCOL WHERE DBIREL = 'Y' AND DBIATR 'IX' If that file is missing... I wonder what else is going on??? You might try re-installing the "extended base option" of the OS... 5722-ss1 option 1.
    0 pointsBadges:
    report
  • Iseriesguy2
    syscolumns is a logical file in library qsys2 based on file Qadbifld. It looks to have a sequel statement in the logical file itself. Try addidng qsys2 library to your library list. You can also do a wrkobj qsys2/syscolumns to verify if it is still in library qsys2. I have included a copy of the sql statement that is in the syscolumns LF: Number of members . . . . . . . . . . . . . : 1 SQL view create statement . . . . . . . . . : CREATE VIEW SYSCOLUMNS (COLUMN_NAME FOR "NAME", TABLE_NAME FOR "TBNA ME", TABLE_OWNER FOR "TBCREATOR", ORDINAL_POSITION FOR "COLNO", DATA _TYPE FOR "COLTYPE", "LENGTH", NUMERIC_SCALE FOR "SCALE", IS_NULLABL E FOR "NULLS", IS_UPDATABLE FOR "UPDATES", LONG_COMMENT FOR "REMARKS ", HAS_DEFAULT FOR "DEFAULT", COLUMN_HEADING FOR "LABEL", "STORAGE", NUMERIC_PRECISION FOR "PRECISION", "CCSID", TABLE_SCHEMA FOR "DBNAME ", COLUMN_DEFAULT FOR "DFTVALUE", CHARACTER_MAXIMUM_LENGTH FOR "CHAR LEN", CHARACTER_OCTET_LENGTH FOR "CHARBYTE", NUMERIC_PRECISION_RADIX FOR "RADIX", DATETIME_PRECISION FOR "DATPRC", COLUMN_TEXT FOR "LABE LTEXT", SYSTEM_COLUMN_NAME FOR "SYS_CNAME", SYSTEM_TABLE_NAME FOR "S YS_TNAME", SYSTEM_TABLE_SCHEMA FOR "SYS_DNAME", USER_DEFINED_TYPE_SC HEMA FOR "TYPESCHEMA", USER_DEFINED_TYPE_NAME FOR "TYPENAME" ) AS SE LECT DBILFL, DBILFI, VARCHAR(STRIP(DBIOWN),128), INTEGER(DBIPOS), DB ITYP, DBIFLN, INTEGER(DBINSC), DBINUL, DBIUPD, DBIREM, DBIDFI, DBIHD G, DBIILN, INTEGER(DBINLN), DBICCC, DBILB2, DBIDFT, DBICNC, DBICLN, INTEGER(DBIRDX), INTEGER(DBIDLN), DBITXT, DBIFLD, DBIFIL, DBILIB, DB ITLB, DBITLN FROM QSYS.QADBIFLD WHERE DBIREL = 'Y' AND DBIATR 'IX
    0 pointsBadges:
    report
  • HowieK
    By doing a DSPFD on SYSCOLUMNS, I was able to display the following statement: (I am on 5.1) CREATE VIEW SYSCOLUMNS (COLUMN_NAME FOR "NAME", TABLE_NAME FOR "TBNA ME", TABLE_OWNER FOR "TBCREATOR", ORDINAL_POSITION FOR "COLNO", DATA _TYPE FOR "COLTYPE", "LENGTH", NUMERIC_SCALE FOR "SCALE", IS_NULLABL E FOR "NULLS", IS_UPDATABLE FOR "UPDATES", LONG_COMMENT FOR "REMARKS ", HAS_DEFAULT FOR "DEFAULT", COLUMN_HEADING FOR "LABEL", "STORAGE", NUMERIC_PRECISION FOR "PRECISION", "CCSID", TABLE_SCHEMA FOR "DBNAME ", COLUMN_DEFAULT FOR "DFTVALUE", CHARACTER_MAXIMUM_LENGTH FOR "CHAR LEN", CHARACTER_OCTET_LENGTH FOR "CHARBYTE", NUMERIC_PRECISION_RADIX FOR "RADIX", DATETIME_PRECISION FOR "DATPRC", COLUMN_TEXT FOR "LABE LTEXT", SYSTEM_COLUMN_NAME FOR "SYS_CNAME", SYSTEM_TABLE_NAME FOR "S YS_TNAME", SYSTEM_TABLE_SCHEMA FOR "SYS_DNAME", USER_DEFINED_TYPE_SC HEMA FOR "TYPESCHEMA", USER_DEFINED_TYPE_NAME FOR "TYPENAME" ) AS SE LECT DBILFL, DBILFI, VARCHAR(STRIP(DBIOWN),128), INTEGER(DBIPOS), DB ITYP, DBIFLN, INTEGER(DBINSC), DBINUL, DBIUPD, DBIREM, DBIDFI, DBIHD G, DBIILN, INTEGER(DBINLN), DBICCC, DBILB2, DBIDFT, DBICNC, DBICLN, INTEGER(DBIRDX), INTEGER(DBIDLN), DBITXT, DBIFLD, DBIFIL, DBILIB, DB ITLB, DBITLN FROM QSYS.QADBIFLD WHERE DBIREL = 'Y' AND DBIATR 'IX' Hope this helps.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following