Using Unidata 8.2.1 with V6.0 Schema Files: Overview
February 20, 2019
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
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 freeUse 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"
*
ENDDeleting 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!"
ENDCreating 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
