Using UniData 8.2.1 with v6.0 schema files: Overview

If you use the U2 ODBC and/or JDBC client with UniData v8.2.1, you need to ensure that all accounts use schema v6.0 files. If they do not, the applications will not work with your UniData account.

This blog post shows you how to create new UniData accounts with schema v6.0 files, delete corrupt files and recreate v6.0 schema files, and create new schema v6.0 files or upgrade older versions of these files.

Creating new UniData accounts with schema v6.0 files

To create new accounts in UniData v8.2.1, use the newacct command or the XAdmin tool to automatically create new accounts.

Newly created UniData accounts contain the necessary default system files and schema v6.0 files, as shown in the following example:

Directory of C:\U2\ud82\test_newacct

[.]                      [..]                     [BP]                     [CTLG]

D_BP                     D_CTLG                   D_DV_SQLColumns          D_DV_SQLSpecialColumns

D_DV_SQLStatistics       D_DV_SQLTables           D_INCLUDE                D_MENUFILE

D_privilege              D_SAVEDLISTS             D_SAVEDLISTSL            D_UD_SQLTABLES

D_VOC                    D__DEBUG_                D__EDAMAP_               D__EDAXMAP_

D__HOLD_                 D__PH_                   D__XML_                  D___SCHEMA__MAP

D___SCHEMA__PROC         D___V__VIEW              [INCLUDE]                MENUFILE

privilege                [SAVEDLISTS]             [SAVEDLISTSL]            [UD_SQLTABLES]

VOC                      _DEBUG_                  _EDAMAP_                 [_EDAXMAP_]

[_HOLD_]                 [_PH_]                   [_XML_]                  __SCHEMA__MAP

__SCHEMA__PROC           __V__VIEW

              30 File(s)        319,488 bytes

              12 Dir(s)  40,621,895,680 bytes free

 

Use the VERIFY_SCHEMA60 tool to verify that all schema files are v6.0.

The default schema owner is set to “0” in the Basic program, which means that the account schema was created by root or a Windows Administrator, as shown in the following example. If the schema account was created by another user, the owner information displays in the privilege file.

PROGRAM VERIFY_SCHEMA60

CALL SCHEMA_VERSION_CHECK(VERSION,MIGRATE.FLAG,ERRORCODE,ERRORMSG)

IF VERSION NE "6000.00.01" THEN

    PRINT "The account schema version ":VERSION:" is invalid for this tool.";STOP

END

* Schema owner: root or administrator

SCHEMA_OWNER = "0"

* Verify all System Schema Files: __V__VIEW, __SCHEMA__MAP and __SCHEMA__PROC

PRINT "1. Verify System Schema Files: __V__VIEW, __SCHEMA__MAP and __SCHEMA__PROC:"

CHECK_LIST1="__V__VIEW":@FM:"__SCHEMA__MAP":@FM:"__SCHEMA__PROC"

LOOP

    REMOVE FILENAME FROM CHECK_LIST1 SETTING MARK

    OPEN "", FILENAME TO FILEX ELSE PRINT "Error: Could not open ":FILENAME:" file"

    CLOSE FILEX

WHILE MARK DO REPEAT

PRINT "1. End of verifying System Schema Files: __V__VIEW, __SCHEMA__MAP and __SCHEMA__PROC."

PRINT ""

* Verify the privilege file:

PRINT "2. Verify the privilege file:"

FILENAME = "privilege"

OPEN "", FILENAME TO FILEX ELSE PRINT "Error: Could not open ":FILENAME:" file"

CHECK_LIST2="SQLColumns":@FM:"SQLSpecialColumns":@FM:"SQLStatistics":@FM:"SQLTables":@FM:"UD_SQLTABLES":@FM:"__SCHEMA__MAP":@FM:"__SCHEMA__PROC"

LOOP

    REMOVE ITEM FROM CHECK_LIST2 SETTING MARK

    RECORD_ID=SCHEMA_OWNER:"*":ITEM

    READ RECX FROM FILEX, RECORD_ID ELSE PRINT "Error: Could not find the record: ":RECORD_ID

WHILE MARK DO REPEAT

SCHEMA_PUBLIC="PUBLIC"

CHECK_LIST3=CHECK_LIST2:@FM:"__V__VIEW":@FM:"privilege"

LOOP

    REMOVE ITEM FROM CHECK_LIST3 SETTING MARK

    RECORD_ID=SCHEMA_PUBLIC:"*":ITEM

    READ RECX FROM FILEX, RECORD_ID ELSE PRINT "Error: Could not find the record: ":RECORD_ID

WHILE MARK DO REPEAT

CLOSE FILEX

PRINT "2. End of verifying the privilege file."

PRINT ""

*

* Verify the UD_SQLTABLES file:

PRINT "3. Verify the UD_SQLTABLES file:"

FILENAME = "UD_SQLTABLES"

OPEN "", FILENAME TO FILEX ELSE PRINT "Error: Could not open ":FILENAME:" file"

CLOSE FILEX

PRINT "3. End of verifying the UD_SQLTABLES file."

PRINT ""

*

* List the SQL Schema files:

PRINT "4. List the SQL Schema dictionary files: DV_SQLStatistics, DV_SQLTables, DV_SQLColumns and DV_SQLSpecialColumns"

UDTEXECUTE "LIST VOC WITH @ID LIKE DV_SQL..."

PRINT "4. End of Listing the SQL Schema files."

PRINT ""

*

* Verify all System Schema dictionary Files: DV_SQLStatistics, DV_SQLTables, DV_SQLColumns and DV_SQLSpecialColumns

PRINT "5. Verify System Schema Dictionary Files: DV_SQLStatistics, DV_SQLTables, DV_SQLColumns and DV_SQLSpecialColumns:"

*

CHECK_LIST4="DV_SQLStatistics":@FM:"DV_SQLTables":@FM:"DV_SQLColumns":@FM:"DV_SQLSpecialColumns"

LOOP

    REMOVE FILENAME FROM CHECK_LIST4 SETTING MARK

    OPEN "DICT", FILENAME TO FILEX ELSE PRINT "Error: Could not open ":FILENAME:" dictionary file"

    CLOSE FILEX

WHILE MARK DO REPEAT

*

PRINT "5. End of verifying System Schema Dictionary Files:DV_SQLStatistics, DV_SQLTables, DV_SQLColumns and DV_SQLSpecialColumns."

PRINT ""

PRINT "*5. End of verifying program"

*

END

 

Deleting corrupt files and recreating v6.0 schema files

If UniData schema files are corrupted or missing, you need to delete all schema v6.0 files and recreate them. The following example shows you how to use the DELETE_SCHEMA60_FILE Basic program to delete all associated schema v6.0 files.

Note: The privilege file might not be deleted when it is in use. Log out of the account and log back in to determine whether the file was deleted or, run the DELETE.FILE privilege command to manually delete the privilege file.

PROGRAM DElETE_SCHEMA60_FILES

PRINT '*** Check the SCHEMA version ***'

CALL SCHEMA_VERSION_CHECK(VERSION,MIGRATE.FLAG,ERRORCODE,ERRORMSG)

CRT 'Schema Version: ':VERSION

* VERSION = "4000.00.01" or "6000.00.01"

IF VERSION NE "6000.00.01" THEN

    PRINT "The account schema version is invalid for this tool."

    STOP

END

*

PRINT "Do you want to delete the account ODBC schema 6000.00.01 files (Y/N)?"

INPUT RSPN,1_

IF RSPN="Y" OR RSPN="y" THEN

    PRINT "Deleting the account ODBC schema 6000.00.01 files ..."

END ELSE

    PRINT "STOP"

    STOP

END

*

* Step1: Drop the UD_SQLTABLES table

    CMD =  'DROP TABLE UD_SQLTABLES;'

       EXECUTESQL CMD

       EXECUTE 'CLEARSELECT'

*

* Check the UD_SQlTABLES file again

    EXECUTE 'SELECT UD_SQLTABLES'

       IF SYSTEM(11) > 0 THEN

           EXECUTE 'CLEARSELECT'

           STOP "Stop to delete all schema 6.0 files."

       END

*

* Step2: Delete all obsolete subtables or views except UD_SQLTABLES

*

PRINT 'Deleting all obsolete subtables or views:'

* Delete the DV_SQLColumns, DV_SQLSpecialColumns, DV_SQLStatistics, DV_SQLTables files, when the UD_SQLTAbles table was dropped.

UDTEXECUTE 'SELECT VOC WITH @ID LIKE DV_...' 

IF SYSTEM(11) > 0 THEN

    DONE = 0

    LOOP

        READNEXT VOC.ID ELSE DONE = 1

    WHILE NOT(DONE) DO

        PRINT VOC.ID

        PERFORM 'DELETE.FILE ':VOC.ID:' FORCE'

    REPEAT

END

*

* Step3: Clean up the __V__VIEW file

EXECUTE 'SELECT __V__VIEW TO 1'

EXECUTE 'SELECT __V__VIEW WITH @ID LIKE VERSION... TO 2'

EXECUTE 'MERGE.LIST 1 DIFF 2 TO 0'

IF SYSTEM(11)> 0 THEN

    EXECUTE 'DELETE __V__VIEW'

END

*

* Step4: Delete schema 6.0 files except the __V__VIEW file

* Note: unlink privilege error when the file is using - :delete.file privilege

*

* KEYLIST1 = '__SCHEMA__MAP':@FM:'__SCHEMA__PROC':@FM:'privilege':@FM:'__V__VIEW'

KEYLIST1 = '__SCHEMA__MAP':@FM:'__SCHEMA__PROC':@FM:'privilege'

*

LOOP

    REMOVE KEY FROM KEYLIST1 SETTING MARK

       CMD = 'DELETE.FILE ':KEY:' FORCE'

       PRINT CMD

       PERFORM CMD

*  

WHILE MARK REPEAT

*

999:

PRINT ''

PRINT ">>> Deleted all the account schema 6000.00.01 files successfully!"

END

 

Creating new schema v6.0 files or upgrading older versions

There are several different ways to create new schema v6.0 files, or to upgrade older versions to v6.0.

The simplest way is by running the “MIGRATE.SQL” command to upgrade schemas to v6.0. You must log in as a root or administrator to run this command.

Another way is to use Visual Schema Generator (VSG) to create the schema v6.0 files. When you run the VSG tool connected to the UniData server account, it will ask whether you want to upgrade the schema to version v6.0.

New schema v6.0 files can be also created by using a simple Basic program, as shown in the following example:

PROGRAM CALL_SCHEMA_MIGRATE

PRINT "Now Migrating Schema to 6000.00.01"

ErrorCode = ""

ErrorMsg = ""

AbortFlag = 0

CALL SCHEMA_MIGRATE(ErrorCode,ErrorMsg)

GOSUB CHECK_RETURN_CODE

IF (AbortFlag) THEN RETURN

PRINT "Migrated to Schema 6.0 successfully."

RETURN

*

CHECK_RETURN_CODE:

IF (ErrorCode # 0) THEN

    PRINT ErrorMsg

    AbortFlag = 1

END

RETURN

Avatar

Paul Chang 9 Posts

Paul Chang is a Principal Technical Support Engineer at Rocket Software. He is responsible for supporting all U2 common middleware client products, Java products and .NET clients. He has 35 years of experience in computing and holds a master’s degree in Computer Science with an emphasis on Relational Database Design.

0 Comments

Leave a Comment

Your email address will not be published. Required fields are marked *