Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7043

DROP SCHEMA results in ERROR XSAI2

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Workaround
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      OS/400
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer
    • Bug behavior facts:
      Wrong query result

      Description

      DROP SCHEMA schemaname RESTRICT results in:

          ERROR XSAI2: The conglomerate (1,024) requested does not exist.

      Prior to DROP SCHEMA, I did drop all tables in that schema.

      I realize that 1,024 conglomerate number translates into c400.dat file in Derby seq0 directory, and that c400.dat file is indeed missing, and I neither know why nor for how long.

      I ran consistency checks on database using SQL commands from https://wiki.apache.org/db-derby/DatabaseConsistencyCheck to find the bad table(s):

          I isolated the 1,024 conglomerate error to the SYS.SYSSEQUENCES table.

      And I used the following SQL to identify the conglomerate name:  SYSSEQUENCES_HEAP

      SELECT C.CONGLOMERATENUMBER, C.CONGLOMERATENAME, S.SCHEMANAME  FROM SYS.SYSCONGLOMERATES C, sys.sysschemas s  WHERE CONGLOMERATENUMBER = 1024 AND s.schemaid = C.schemaid

      In fact, I cannot even SQL SELECT on SYS.SYSSEQUENCES table without getting the same conglomerate error as the DROP SCHEMA schemaname RESTRICT command.

      In other words, I cannot DROP SCHEMA schemaname RESTRICT until SYS.SYSSEQUENCES corruption is resolved.

      I was wondering if anyone knew how to resolve this easily with either SQL command(s) or any UNIX style java commands.

      P.S.  Keep in mind, that I am not an IJ expert, as I have been using a simple front-end Derby tool created by our manufacturer to run SQL statements on Derby DB, without all the complexity of using IJ and setting up IJ with environment variables, classpaths, etc.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              watts.d.matter Rick Cook
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: