The TOPConnect 4 tool for data access and connection is distributed in distinct builds for different platforms, databases and operating systems, such as Windows, Linux, AIX, HPUX and Solaris.
Aiming to meet the needs of clients who use the DB2/400 database, in the iSeries (AS/400) platform, the TOPConnect 4 application was ported and adjusted to better utilize environment resources and DB2 database in iSeries, allowing the use of SQL stored procedures compiled in the database and eliminating some operational limitations and characteristics implied with the use of the previous version (TOPConnect 2) in environment AS/400.
See below the topics to be approached:
Important |
|
For versions above V5R3, you may need to convert TOPConnect objects. In this case, the operating system may convert them directly, by importing the save file with the TOPConnect 4 subsystem, created for distribution with the observability info data, which allows the operating system itself to make the conversions needed for an upgrade. In the next publication, we will document this procedure and others related to it in greater detail.
The version of TOPConnect 4 for AS/400 does not yet support the following features, available in TOPConnect 4 versions for the other platforms (Windows/Linux):
We are still adapting the monitoring feature to the AS/400 environment, to better use the interprocess communication resources and, once ratified, it will practically have all the features and resources currently available in TOPConnect 4 for the Windows/Linux environments, such as online user monitoring, occurrences log viewing and connection tracing, connection blocking/release and other wizards.
To use TOPConnect 4 in AS/400, you need to install the LIB that contains the application (TOP40), the configure a user in the computer for operational purposes. TOPConnect 4 requires a schema, created with the Data Dictionary resource. For these operations, we recommend user QSECOFR, and following the script below:
The package TOTVS S.A. provides is a save file containing the TOPConnect 4 application for AS/400, found in the TOPConnect 4 Library/SubSystem. To restore it in the client environment, you must create a target save file in the client environment, named TOP40V5R3, to use as target for the restore operation.
To create this LIB, run the following instruction in the command prompt of AS/400.
CRTSAVF FILE(QGPL/TOP40V5R3) TEXT('TOPCONNECT 4.0 Build 20081030')Decompress the package of LIB TOP40, available in ZIP format, in a computer able to connect to AS/400 via FTP.
Example:
In our example, the IP address of access to AS/400 is 10.10.2.1.
ftp> open 10.10.2.1
Connected to 10.10.2.1.
220-QTCP at 10.10.2.1.
220 Connection will close if idle more than 5 minutes.
User (10.10.2.1:(none)): qsecofr
331 Enter password.
Password:
230 QSECOFR logged on.
ftp> binary
200 Representation type is binary IMAGE.
ftp> put TOP40V5R3 QGPL/TOP40
200 PORT subcommand request successful.
150 Sending file to member SAVF in file TOP40V5R3 in library QGPL.
250 File transfer completed successfully.
3527040 bytes sent in 7.74 seconds (346.54 Kbytes/sec)
ftp> bye
RSTLIB SAVLIB(TOP40) DEV(*SAVF) SAVF(QGPL/TOP40V5R3) MBROPT(*ALL) ALWOBJDIF(*ALL)
DLTF QGPL/TOP40V5R3
To execute the application successfully, you must add library TOP40 to the list of search libraries in use. In this case, it is more effective to add the library to the list of Job Description search libraries, also ensuring the correct search order of the library.
To do this task, you must run the following instruction:
CHGJOBD JOBD(TOP40/TOP40) INLLIBL(QGPL QTEMP TOP40)
To run the application, the system administrator must create a user, called TOPCONNECT. This user will have authority over library TOP40 and will administrate this component exclusively.
For this purpose, you must set the following user configurations:
However, it is important for the administrator to ensure the TOPCONNECT user every right over library TOP40, to remove the same configurations from other users and to ensure, to the TOPCONNECT user, the use of the Journal control instruction (CHGJRN).
Procedure
Library TOP40 was developed and created with the "observability" information, which increases the size of the objects a little, though allowing the operating system itself, in versions abover V5R6, to perform adaptations for compatibility in programs and optimizations.
After restoring savefile and installing or updating a version already deployed, of TOPConnect 4 for AS/400, we recommend running the instructions sequence below, so the operating system may adjust the programs, removing observability information and optimizing codes for the version currently in use.
However, be mindful to execute these commands when the TOP40 subsystem is not in use (TOPConnect teminated, without jobs, with nothing online).
CHGPGM PGM(TOP40/ENDTOP40) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGPGM PGM(TOP40/TOPCONNECT) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGPGM PGM(TOP40/TOPCONNFLT) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGPGM PGM(TOP40/TOPCONNJOB) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGPGM PGM(TOP40/TOPCONNQRY) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGPGM PGM(TOP40/TOPRUNTIME) OPTIMIZE(*FULL) RMVOBS(*ALL)CHGSRVPGM SRVPGM(TOP40/TOPRUNTIME) OPTIMIZE(*FULL) RMVOBS(*ALL)
Then, note the example of schema creation, using the iSeries Navigator application in Microsoft Windows environment or directly in AS/400.
Windows Environment
AS/400 environment
To start the application, use command TOPCONNECT.
Command TOPCONNECT allows additional parameters to specify the quantity of connection jobs to be started along the application and the minimum quantity of free jobs in the connections pool, through parameters STRJOBS and JOBS, respectively.
Example: TOPCONNECT STRJOBS(20) JOBS(4)
In this example, with the parameters above, TOPConnect starts up with 20 processes (TOPCONNJOB) in the connections pool. Insofar as these processes are receiving connections, the application makes sure that at least 4 processes are free for new connections.
However, if you do not fill out either parameter, the default of both is 2 initial jobs and 2 free jobs.
To close the TOPConnect application, use command ENDTOPCONN. When you execute command ENDTOPCONN, the jobs in use and free in the connections pool are notified to close and, at last, the main TOPConnect job is closed.
However, to check whether is running any jobs, use instruction Work with active Jobs.
Example: WRKACTJOB SBS(TOP40)
To use TOPConnect 4 for AS400, you must set the TOTVS Application Server configuration file, in a manner similar to the use of TOPConnect 4 for other databases.
To do this, specify in keys Database and Alias, of section [TOPConnect], the following data:
Example:
[TOPCONNECT]Database=DB2Server=172.16.0.0Alias=TOPTESTPort=7890
When you use TOPConnect 2 for AS/400, functions TCSrvType() and TCGetDB() return, respectively, [AS/400] and [DB2/400]. When we use TOPConnect 4 in this platform, these functions return, respectively, [iSeries] and [DB2/400].
Thus, you can identify, in the AdvPL application, exactly which DB2 is in use, if you need specific or differentiated procedures for this environment.
The use of SQL engine, of DB2, for AS/400, lets you use table names with more than 10 characters, though TOPConnect does not yet support opening a table named with more than 10 characters in compatibility mode (DBUseArea()).
Using tables with more than 10 characters in the name, the operations of creation (DBCreate()), deletion (TCDelFile()) and use for queries are supported without problems. But opening tables, through function DBUseArea(), is not yet supported. We are currently analyzing this possibility.
To monitor TOPConnect4 in the AS/400 iSeries environment, you need to use a version of TOPMonitor in another platform (Windows and/or Linux). Distinctly from other platforms, in which you configure the connection port in the application (default 7890), to monitor the AS/400 iSeries, you must add a unit to the configured port. In a default configuration, in which TOPConnect expects connections from TOTVS Application Server in port 7890, TOPMonitor must connect with TOPConnect through port 7891.
Important |
Even though TOPMonitor is not yet ratified, you can use it. Sooner or later, TOPMonitor "freezes", when competing processes move up and down in TOPConnect 4 of AS/400, so you may have to directly close TOPMonitor in the System Manager of the operating system of the remote computer you are using. |
In AS/400, TOPConnect manages MEMO fields in a separate table, created exclusively for this purpose. Hence, if you execute a query such as SELECT *, for example, in a table containing MEMO fields, the columns related to the fields are returned as numeric, containing a reference/identifier of the field in MEMO fields table, of internal and exclusive use of TOPConnect 4.
Similarly to other platforms, you must recover the content of MEMO fields by selecting in the table that contains the datum, not through queries.
When you run routines for updating and data input, and errors occur in these operations, TOPConnect creates an error log. Through this file, you can identify the cause of the error by the codes returned.
Basically, two error types exist with the following prefixes:
TOPConnect records errors with the SQL prefix with an additional description it gets directly from AS/400, explained the possible causes of the occurrence and corrective actions. Errors with CPF prefix lack this information. In this case, you can get the detail on the occurrence, in AS/400, by using command DSPMSGD followed by the occurrence code.
Exemplo 1
In the following example, we executed an instruction to create a table; however, the table already exists. Notice how TOTVS Application Server returns this occurrence.
/*-------------------------------------------------------
ERRO THREAD ([5796], juliow, TEC-AUTOQUAD) 01/06/2008 18:25:38
Stack :
MYTEST: TOP Error SQL0601 - â User Name : JULIO - Comment: DB2/TOP4STRESSã( From tDBServer::CreateFile )( STMT CREATE TABLE TOP4STRESS.MYTEST(CPOC CHAR(10) NOT NULL WITH DEFAULT ' ',CPON DOUBLE NOT NULL WITH DEFAULT 0.0,CPOD CHAR(8) NOT NULL WITH DEFAULT ' ',CPOL CHAR(1) NOT NULL WITH DEFAULT 'F',CPOX CHAR(1) NOT NULL WITH DEFAULT ' ',CPOM INTEGER NOT NULL DEFAULT 0,D_E_L_E_T_ CHAR(1) NOT NULL WITH DEFAULT ' ',R_E_C_N_O_ DOUBLE NOT NULL WITH DEFAULT 0.0) ) - MYTEST in TOP4STRESS type *FILE already exists.Cause . . . . . : An attempt was made to create MYTEST in TOP4STRESS or to rename a table, view, alias, or index to MYTEST, but MYTEST already exists. All tables, views, aliases, indexes, SQL packages, sequences, constraints, triggers, and user-defined types in the same schema must have unique names. -- If MYTEST is a temporary table, it cannot be replaced unless the WITH REPLACE clause is specified. -- If the schema name is *N, this is a CREATE SCHEMA statement. If this is a CREATE TABLE or ALTER TABLE statement and the type is *N, MYTEST is a constraint. Recovery . . . : Change MYTEST to a name that does not exist, or delete, move, or rename the existing object. If this is a temporary table, use the WITH REPLACE clause. If creating an SQL package, specify REPLACE(*YES) on CRTSQLPKG. Try the request again. on CTMCREATE(MEMTEST.PRW) 27/02/2008 line : 315
[build:7.00.080307A]
[environment: advpltests_top4_as400]
[thread 5796]
Called from {|| (CTMCREATE())}(MEMTEST.PRW) line : 100
Called from line : 0
Called from U_CTMTEST(MEMTEST.PRW) 27/02/2008 line : 203
-------------------------------------------------------*
The SQL error code is returned at the beginning of the message, followed by the thread/connection data in TOP (user and process comments), the executed statement that presented error and the full description of the error message.
Important |
The message returned to AdvPL is limited to 4 Kb. Hence, if the statement surpasses this size, the end of the message, containing the full description of the error, is not saved. In this case, you must check the errors log file (topconn.log) of TOPConnect 4. In AS/400, this file is saved in folder /root or in the file system folder from which the application started. |
Exemplo 2
In the following example, an input operation and a record update operation violated the unique key index of the file at issue, in which the environment and the table involved in the operation are colored red and, in parentheses, the error code of the operation.
/*-------------------------------------------------------
ERRO THREAD (julio, TEC-AUTOMAN) 15/08/2007 13:53:34
Stack :
PKTEST: DB error (Insert): -27 File: PKTEST - TOP Error 3102 - Write Error TOPDBA/PKTEST (CPF5034)Ô User Name : JULIO - Comment: DB2/TOPDBAÒ( From tAS400ISAMFile::Write ) on U_PKTEST(PKTEST.PRW) 15/08/2007 line : 40
[build:7.00.070518A]
[environment: advpltests_top_ctree]
[thread 5904]
-------------------------------------------------------*/
/*-------------------------------------------------------
ERRO THREAD (julio, TEC-AUTOMAN) 15/08/2007 1:59:11 PM
Stack :
PKTEST: DB error (Update): -29 File: PKTEST - TOP Error 3102 - Update Error PKTEST : 2 (CPF5034)Ô User Name : JULIO - Comment: DB2/TOPDBAÒ( From tAS400ISAMFile::Update ) on U_PKTEST(PKTEST.PRW) 15/08/2007 line : 58
[build:7.00.070518A]
[environment: advpltests_top_ctree]
[thread 352]
-------------------------------------------------------*/
To check the description of message CPF5034, through the AS/400 prompt, you must execute command:
DSPMSGD CPF5034
The system displays the following information:
Message ID . . . . . . . . . : CPF5034
Message file . . . . . . . . : QCPFMSG
Library . . . . . . . . . : QSYS
Message . . . . : Duplicate key on access path.
Cause . . . . . : An output or update operation on record number &6 record
format &7 member number &8 failed because of a duplicate key in a unique
keyed member that is based on member &4 file &2 in library &3, or in a
based-on member of &4. The failure could also have been caused by an output
or update operation done to another file by a trigger program associated
with file &2 in library &3.
Recovery . . . : See previously listed message CPF5009 to identify the file and record with the duplicate key and change the key value so that it is
unique. Note that the file could be a file operated on by a trigger program. Then try your request again.
To migrate the version, you need to create a new schema to access the data, and then migrate the data from the old library to the new schema.
The migration procedure involves the exporting of the database to a local RDD, through TOPConnect 2, using APSDU. To import these data to the new database, through TOPConnect 4, you must use TOPConnect 4 and APSDU.
With the changes made to table columns and stored procedures, if a production environment already exists with TOPConnect 4 AS/400 already created, you must follow the migration procedure from TOPConnect 2 to TOPConnect 4, exporting the data to another RDD, creating the tables again through the ERP and importing the data again, using APSDU.
Currently, in the ERP application, special procedures and protections for some operations exist when the TOPConnect Server used is in an AS/400 environment. These protections are checked by comparing the return of function TCSrvType(), and in most data the protections were placed to avoid the use of the queries engine of TOPConnect 2 in AS/400, given the operational limitations.
Using TOPConnect 4 for AS/400, function TCSrvType() returns the string “iSeries”, because in this version of TOPConnect 4, this behavior limitation no longer exists; that is, no further changes to the AdvPL code are needed.
To make the execution environment for queries and stored procedures compatible in AS/400, we changed the stored procedure application functions and function ChangeQuery() to exchange the occurrences of || (pipe pipe) for CONCAT, the operator!=(different) for <> and to add to the end of the query, the statement "FOR READ ONLY", already previously added to queries for AS/400 when TOPConnect 2 is used.
As we changed data table fields CHAR and VARCHAR, from build 20081008 onward, to use CCSID 1280, we also changed the stored procedures to create them with these specifications in Input/Output fields. To successfully apply procedures, you need the patch of program CFGX051, procedures application, with date equal to or after 08/23/2008.
Moreover, we adjusted function IndRegua() to convert function call Empty(field), when using the filter in TOPConnect 4 environment for AS/400, and adjusted TOTVS Application Server to run AdvPL applications correctly in TOPConnect 4. To accomplish this, the TOTVS Application Server build must be equal to or above MP8 - Build 7.00.070910P - Sep 25 2007 - 16:52:23.
TOPConnect 4 for AS/400 allows running SQL instructions and queries in a manner similar to DB2 UDB for the other platforms, including the application of stored procedures package for DB2 available in the Microsiga Protheus ERP. To make this operation possible, the AS/400 database must be a schema created with Data Dictionary.
To eliminate the need to use a specific Sort Sequence table and to eliminate conversions between ASCII and EBCDIC and vice versa between TOTVS Application Server and TOPConnect, the control tables (TOP_FIELD, TOP_PARAM) and the data tables used by the ERP, through TOPConnect, are created by explicitly using CCSID 1208 (UTF-8) for all data columns of character type.
If you use TOPConnect for integration with other systems, it can only read and save data tables inside a schema, and with these characteristics. TOPConnect 4 cannot interact with a database/environment created with version TOPConnect 2. If an application external to TOPConnect 4 uses the tables it created for reading and/or saving operations, you must check whether the application needs adaptations to operate properly.
The TOPConnect 4 application for AS/400 iSeries was encoded to make data access in other platforms compatible, in environments with relational databases, in which the use of queries and stored procedures encoded in SQL syntax make development easier, and execute processes to take advantage of the processing capacity of the database. This new version of the application also offers new monitoring resources and record lock control, not available in the previous version, and solves non-conformance occurrences and utilization limits.
TOPConnect 4, in relation to TOPConnect 2, with the new resources available, very closely approaches the performance of the previous version, with an average increase in CPU usage between 20% and 30%. The most critical points of the application, which experience elevated CPU usage peaks in both versions, are the executions of queries and the filters engine. These points are worthy of attention when you evaluate the performance of the application. By analyzing the process and the actual processing instruction, you can take database "tuning" actions and/or adapt the application source-code to get a better performance at a lower processing cost.