Date: 03-31-2011 Subject: RELEASE 9.5 DATA Manager Files These release notes pertain to the following programs or files: SUNDM.EXE 9.5 31 Mar 2011 9,5,0,500 SUNWSRV.DLL 9.5 31 Mar 2011 9,5,0,500 *============================================================================== The following files have been changed as noted: ------------------------------------------------------------------------------- SUNDM - SQLIO support has been added to the Sunbelt Data Manager. The SQLIO features allow normal PLB IO instructions to be used where io operations are performed to SQL database tables. Before the SQLIO operations can be performed, user schema information must be created/provided to specify the record layouts which define the field positions that correspond to a column in a SQL table. The schema files for the SQLIO support must exist on the Data Manager server system where they can be loaded using a SCHEMA instruction. Definitions SQLIO Sub system that has been implemented into the Sunbelt Data Manager to allow PLB IO instructions to be executed using SQL language statements to access and manipulate SQL database tables. SQLIO Schema Text file using the XML data format that specifies the file names and record layouts that defines how SQL database table columns are to be associated and used during the SQLIO operations. SQLIO System Tables SQL database tables that contain the SQLIO schema information and are used to during the execution of PL/B instructions for the SQLIO implementation. The system table are automatically created if they do not exist when SQLIO schema data is being loaded. The SQLIO system tables are named: Sun_Sqlio_Afile Sun_Sqlio_alias Sun_Sqlio_Columns Sun_Sqlio_Databases Sun_Sqlio_File Sun_Sqlio_Ifile Sun_Sqlio_Version Data Manager CFG Keywords DM_SQLIO_HOST={hostname} This keyword defines the initial/default SQL database source that is connected to when executing SQLIO operations. The {hostname} string is unique to the SQL database engine that is connected. If this keyword is not specified, the Data Manager looks for the DM_SCHEMA keyword to get a default SQLite database source. If the DM_SCHEMA keyword is not found, the Data Manager uses the default SQLite database source named 'sunschema.db' that is located with the Data Manager executable. DM_SQLIO_USER={username} This keyword defines the username is used to make the connection. This keyword may or may not be required to make a connection. The explicit behavior invoked by this keyword is the same as described for the {user} parameter in the DBCONNECT instruction. DM_SQLIO_PASS={password} This keyword defines the password for the user that is used to make the connection. The explicit behavior invoked by this keyword is the same as described for the {pass} parameter in the DBCONNECT instruction. DM_SQLIO_CONN={login} This keyword defines additional login information that may be needed to make the connection. The explicit behavior invoked by this keyword is the same as described for the {conn} parameter in the DBCONNECT instruction. DM_SQLIO_EXT={extension} This keyword defines optional extension data that is used to specify some unique parameters used during the connection process. The explicit behavior invoked by this keyword is the same as described for the {ext} parameter in the DBCONNECT instruction. DM_SQLIO_FLAGS={flags} This keyword defines any special settings used to make the connection. The explicit behavior invoked by this keyword is the same as described for the {flags} parameter in the DBCONNECT instruction. DM_SQLIO_DRIVER={drivernumber} This keyword defines a driver number that identifies the type of SQL connection to be used. The {drivernumber} is a numeric value from 1 to nnn that specifies the SQL database connection type. If this keyword is not specified in the configuration CFG file, the {drivernumber} defaults a value of 1. {drivernumber} Database Connection Type 1 SQLite native connection 2 DBFILE connection DM_SQLIO_SQLTYPE={sqllanguagenumber} This keyword defines the type of SQL syntax to be used. If this keyword is not specified, the {sqllanguagenumber} defaults to a value of 1. This keyword is required to ensure proper execution of SQL statements to perform the PLB IO instructions relative to a specific SQL database engine type. {sqllanguagenumber} Language Descriptions 1 SQLite language type 2 SQL Server language type 3 MYSQL language type DM_SQLSCHEMADB={dbname} This keyword defines a schema database name found in the Sun_Sqlio_Databases SQLIO system table. This keyword defines the database name where current schema information is to be accessed and retrieved. DM_SQLTABLEDB={tabledbname} This keyword defines a schema database name found in the Sun_Sqlio_Databases SQLIO system table. This keyword defines the database name where current application SQL data tables are accessed and used. DM_SQLIO_DEBUG_LOG={logname} This keyword is only used when debugging issues for the SQLIO support. The {logname} is a file name with path where the runtime writes SQL statements that are executed when performing the PLB IO instructions. If the leading character for the {logname} is a '>' character, only SQL statement execution errors are appended to the log file. If the leading character for the {logname} is a '+' character, all new log entries are appended to the log file. For the Data Manager all users using SQLIO are logging to the same log file. Getting Started 1. Add a DM_SQLIO_HOST, DM_SQLIO_DRIVER, and DM_SQLIO_SQLTYPE keyword to the SUNDM CFG file. These keywords specify the database source, driver type and SQL language syntax type used to make a connection to a SQL database engine. 2. Define the record layout and create a SQLIO XML schema file. See the 'Schema File Format' section for more information. The SQLIO XML schema file must be located on server where the Data Manager is executing. 3. Modify a PLB program as follows: A. Load a SQLIO schema using the SCHEMA instruction where the Data Manager redirection is used. The SQLIO schema only needs to loaded once or as needed when SQLIO schema updates are required. Example: . $DMNAME DIM 100 MOVE "default|127.0.0.1", $DMNAME SCHEMA $DMNAME, IMPORT="myschema.xml", FLAGS=10 . SCHEMA $DMNAME, IMPORT="myschema.xml", FLAGS=12 B. Modify the OPEN and/or PREP instructions to include the tag with or without attributes keywords and include the redirection '|' data to access the Data Manager. The tag identifies that a database table is being accessed for use at the Data Manager. The tag is included in the file name fields of the instructions and it must be followed with the '|' redirection to access the Data Manager. Example: xI IFILE . OPEN xI, "test.isi|127.0.0.1" ... ....... OPEN xI: "test.isi|127.0.0.1" ... ....... PREP xI, "test.txt": "test.isi|127.0.0.1", "5", "10" ... ....... PREP xI,"test": "test|127.0.0.1","5","10" ... C. Compile and execute the PLB program to access a Data Manager. SQLIO Schema Administrative Tables The description for the Data Manager is the same as described for the Sunbelt runtimes. Schema File Format The description for the Data Manager is the same as described for the Sunbelt runtimes. PLB Language Changes The description for the Data Manager is the same as described for the Sunbelt runtimes except the Data Manager ip/url redirection string starting with the '|' character follows the tag. Limitations for SQLIO The description for the Data Manager is the same as described for the Sunbelt runtimes. SQL Database Engine Notes The description for the Data Manager is the same as described for the Sunbelt runtimes. Error Codes The description for the Data Manager is the same as described for the Sunbelt runtimes. Examples to access Data Manager Example of SQLIO settings in Sundm CFG file [environment] ###################################################### # SQLIO Error are appended to log file 'sqldebug.txt' # Only needed to debug/resolve an SQL error # #DM_SQLIO_DEBUG_LOG=>sqldebug.txt # ###################################################### # SQLite Driver # DM_SQLIO_HOST=c:\sunbelt\DMtest\database\tsqlqaDM.db DM_SQLIO_DRIVER=1 # ###################################################### # SQL Server # #DM_SQLIO_HOST=DBADO;;DSN=SqlIoDev #DM_SQLIO_DRIVER=2 #DM_SQLIO_SQLTYPE=2 #DBADO=sunwado.dll # ###################################################### Example of SQLIO Schema named 'orders.xml' Note: The 'orders.xml' MUST be located on the system where the SUNDM Data Manager is executing. SqLiteTest c:\sqlio_test\tsqlio.db 1 1 Test database SqlSrvTest DBADO;;DSN=SqlIoDev 2 2 Test database Orders.aam Orders.txt 5-9 Orders.isi Orders.TXT Orders custid Orders.TXT Orders ORDERID 1 4 0 0 0 1 0 0 CUSTID 5 5 0 0 0 1 0 0 PRODID 10 3 0 0 0 1 0 0 ORDERDATE 13 8 0 0 0 1 0 0 ORDERTIME 21 8 0 0 0 1 0 0 QUANTITY 29 5 0 0 0 1 0 0 Example of Program using SQLIO Schema to Create Orders SQL Table OrdersIn FILE OrdersOut FILE . SEQ FORM "-1" D50 DIM 50 . Schema INIT "orders.xml" . SCHEMA "default|127.0.0.1", IMPORT="orders.xml": FLAG=10 ;Replace mode & SQLIO . . Drop existing SQL table at Data Manager! . ERASE "orders.txt|127.0.0.1" . . Existing txt file on Data Manager! . OPEN OrdersIn, "orders.txt|127.0.0.1" . . SQL table on SQL database using the Data Manager server! . PREP OrdersOut, "orders.txt|127.0.0.1" . LOOP READ OrdersIn, SEQ; *LL, D50 BREAK IF OVER WRITE OrdersOut, SEQ; *LL, D50 REPEAT . CLOSE OrdersIn CLOSE OrdersOut Example of Program to READKS Orders Table Orders IFILE . Orderid DIM 4 Custid DIM 5 Prodid DIM 3 Orderdate DIM 8 Ordertime DIM 8 Quantity DIM 5 . Schema INIT "orders.xml" . SCHEMA "default|127.0.0.1", IMPORT="orders.xml": FLAG=10 ;Replace mode & SQLIO . . Open 'orders' table and cache 10 rows at a time. . OPEN Orders, "orders.isi|127.0.0.1" . LOOP READKS Orders; Orderid: ;Readks SQL table! Custid: Prodid: Orderdate: Ordertime: Quantity BREAK IF OVER DISPLAY *LL, "Orderid...:", Orderid, "...": "Custid....:", Custid REPEAT . CLOSE Orders - Modified to correct a secondary replication hanging problem that could occur when the master control list verification was interrupted by a communications error to the primary replicator. - Corrected a problem where the enhanced COPYFILE/COPYNEXT instructions would not properly copy a file that was not larger than 4GB. - Corrected a problem where the COPYBYTES instruction would report an invalid size after a file larger than 4GB was copied. - Corrected a GPF problem for the HTTP instruction that was caused by a buffer overflow when using the *POSTMESSAGE option. - Corrected a problem where the COUNTKEYS instruction was returning an erroneous key count when the start key was greater than any keys in the ISI file. A value of zero should have been returned in this case. ------------------------------------------------------------------------------- SUNDM (Windows) - Modified the icon tray statistics output to include the Data Manager serial number. - Modified the icon tray statistics for Sundm to allow the Replication state to be reported as 'Online as Backup'. - Modified the Windows Data Manager to log system information about the processor where SUNDM is executing. - Modified the startup processing to report the server OS information in the log file as follows: Processor Architecture Number of Processors Active Processor Mask Processor Type Processor Speed This information is being reported in the log file to aid in the evaluation of performance issues that may arise. ------------------------------------------------------------------------------- SUNDM REPLICATION SUPPORT - Modified the replication startup file sync processing to minimize any interaction with a PLB program that is opening a file on the Data Manager. - Modified to better identify the secondary replicator that has stopped communicating with the primary or has logged off from the primary by including the secondary replicator serial number in the error message. 'Secondary replicator 'Nxxxxxx' has stopped communicating.' and 'Secondary replicator 'Nxxxxxx' logoff.' - Corrected a problem where the last modified file time was not being updated at a secondary/backup replication server after an unmanaged file was changed/touched at the primary replication server. - Modified to prevent the keep alive time checking until after the first transaction request is received by the primary replicator. This corrects a problem where the primary was closing the socket connection to a secondary/backup during the startup synchronization phase where a secondary/backup did not communicate for more than 5 minutes while it was processing the file verification list. - Corrected a problem where a secondary/backup replicator would not terminate or finish the startup sync phase until after the primary was terminated when the '-f' command was used by the secondary/backup replication server. -------------------------------------------------------------------------------