What is a concat file?
Concat files in T24 are kind of index files that are generally used in local jBC code to speed up queries on large data files. The concat files are updated at application level and the content must be in line with associated data files. These files are widely used in T24 banking system.
A typical concat file looks as follows:
| CT FBNK.STMT.VAL.ENTRY 432734-20070817 432734-20070817 001 144740000650673.000001 002 144740000650673.000002 003 144740000650672.040001 004 144740000650672.040002 005 144740000650672.020001 006 144740000650672.020002 007 144740000650672.000001 008 144740000650672.000002 jsh demo ~ --> |
In provided example, FBNK.STMT.VAL.ENTRY is a concat file associated to the data file FBNK.STMT.ENTRY. The indexation criteria (record ID) is based on account number (432734) and transaction value date (20070817). The record contains a list of accounting entries (ID references from FBNK.STMT.ENTRY) for the given criteria. Hence, any query on FBNK.STMT.ENTRY file with the selection criteria on a particular value date and account number will return results many times faster if the query engine will use FBNK.STMT.VAL.ENTRY concat file.
How can I speed up my query?
Concat files can be efficiently used by the driver in order to extract data much faster. Developers may write a plugin routine and attach it to specific table, and the driver will trigger it when a query will be executed on that table. The plugin is a jBC routine (we'll call it index subroutine) and is being attached to the data file using Vultar Administrator tool.
Important: T24 users may input file name without prefix FXXX (where XXX is the company mnemonic). In this case, in multi-company environments, the driver will choose the file based on the company specified in DSN dialog of the ODBC Administrator. It should be noted, the index subroutine examples from the installation kit are for demo purposes only, and could be replaced with the locally developed ones.
Each time users request data from large files, the index subroutines will be called by the driver which will return the list of IDs obtained from concat files. Then, the driver will pass the list to jQL SELECT using PASSLIST. A large file may have several concat files (and respectively several indexation criteria), and it is up to the programmer to decide whether the index routine will use them or not. The programmers have a set of common variables available which contain where_conditions structured in usable arrays. Based on that data, the index subroutine may choose proper concat file for faster results.
The installation kit of the driver comes with two index definitions, for ACCOUNT file and STMT.ENTRY file. See below an example with the ACCOUNT file (VT.CONCAT.ACCOUNT routine). The driver will use CUSTOMER.ACCOUNT concat file each time the users request the data with selection criteria based on CUSTOMER field.
The more complex VT.CONCAT.STMT routine from installation kit performs more checking, and uses several concat files, depending on the selection criteria sent by users. The source code of the index subroutines is available in the installation folder of the driver’s database module.
| 001 SUBROUTINE VT.CONCAT.ACCOUNT 002 ********************************************* 003 * Builds VT.ID.LIST based on concat file CUSTOMER.ACCOUNT in case 004 * the customer is specified as a selection criteria 005 006 007 $INCLUDE GLOBUS.BP I_COMMON 008 $INCLUDE GLOBUS.BP I_EQUATE 009 $INSERT I_VT.COMMON 010 ********************************************* 011 VT.CONCAT.USED = 0 012 LOCATE "CUSTOMER" IN VT.FIELDS, 1 SETTING CUS.POS THEN 013 ENQ.OPERAND = "" 014 CALL VT.CONVERT.OPERAND(VT.LOGICAL.OPERANDS<CUS.POS>, ENQ.OPERAND) 015 016 IF ENQ.OPERAND = "EQ" THEN 017 VT.CONCAT.USED = 1 018 019 CUS.ID = VT.RANGE.AND.VALUE<CUS.POS> 020 CHANGE @SM TO @FM IN CUS.ID 021 022 FN.CUSTOMER.ACCOUNT = "F.CUSTOMER.ACCOUNT" 023 CALL OPF(FN.CUSTOMER.ACCOUNT, F.CUSTOMER.ACCOUNT) 024 025 VT.I.COUNT = DCOUNT(CUS.ID, @FM) 026 FOR VT.I = 1 TO VT.I.COUNT 027 READ R.CUS.ACCOUNT FROM F.CUSTOMER.ACCOUNT, CUS.ID<VT.I> THEN 028 VT.ID.LIST<-1> = R.CUS.ACCOUNT 029 END 030 NEXT VT.I 031 END 032 END 033 034 RETURN 035 END 036 |
The following common variables are available for developers if I_VT.COMMON file is included in the routine:
| VT.FIELDS | A dynamic array with the list of fields used in the selection criteria. | ||||||||||||||||||||
| VT.LOGICAL.OPERANDS |
A dynamic array with the list of numerical logical operands. The following list is supported:
|
||||||||||||||||||||
| VT.RANGE.AND.VALUE | A dynamic array with the list of elements with one or more values. Each element may have several values (in case of OR or GE/LE constructions). The values within the element are delimited with @SM. | ||||||||||||||||||||
| VT.ID.LIST | An output variable which contains the list of IDs retrieved from the concat files. | ||||||||||||||||||||
| VT.CONCAT.USED |
An output boolean variable with the following values:
|
Explaining the above subroutine:
- The subroutine locates CUSTOMER field in the selection criteria.
- It makes a call to VT.CONVERT.OPERAND in order to convert the numeric value of VT.LOGICAL.OPERANDS into more readable text equivalent (optional).
- Only EQ operands are allowed for concat processing.
- Finally, it reads the content of F.CUSTOMER.ACCOUNT file, and fills in the VT.ID.LIST array.
Any SQL request on ACCOUNT file will trigger the above subroutine. If where_conditions of the SQL phrase will contain a logical expression with CUSTOMER field and EQ operand, the concat file will be used. Otherwise direct jQL SELECT on the ACCOUNT file will be performed.
Warning: In some special cases, the common variable VT.FIELDS might be empty. This can happen due to the restrictions imposed by the driver for where_conditions which would include “OR” conditions on different fields. As a workaround, where_conditions should be redesigned.