Writeb can optimize the performance of a record writing loop in a database table.
Writeb [ class ]
Element | Description | Restrictions |
class | Table abbreviation, by default the default table | The table must be opened by a File order. |
Call DEBTRANS From GLOCK
adxwrb=10 : # Value
$TR1
[V]GOK = 1 : Trbegin [SOH] : # Transaction start
# Loop on both tables (each possessing a buffer)
For nolign=1 to [M]LINNBR
[F:SOP]=[M] : Writeb [SOP] : # Write with buffer
If [S]fstat<>0
If adxwrb=1 : Call FSTA(“SOP”) From GLOCK : Endif : The erroneous line is known
Break : # error on one of the lines
Endif
[F:SOQ]=[M] : Writeb [SOP] : # Write with buffer
If [S]fstat<>0
If adxwrb=1 : Call FSTA(“SOP”) From GLOCK : Endif : The erroneous line is known
Break : # error on one of the lines
Endif
Next nolign
If [S]fstat<>0 : Goto ROL_TR1 : Endif : # Error on one of the lines
# Make sure all the lines [SOP] and [SOQ] have been written
If adxwrb>1
Flush [SOP] : If [S]fstat<>0 : Goto ROL_TR1 : Endif
Flush [SOQ] : If [S]fstat<>0 : Goto ROL_TR1 : Endif
Endif
# Write header
[F:SOH]=[M]
Write [SOH] : # no stacking here, a single one is written
If [S]fstat<>0
Call FSTA(“SOH”) From GLOCK : Goto ROL_TR1 : # Error on header
Endif
Commit
Return : # Everything is OK
# In case of error, if you were in grouped mode, proceed again to identify the erroneous line
$ROL_TR_1
Rollback
If adxwrb>1 : adxwrb=1 : Goto TR1
GOK=0
Return
Writeb makes it possible to buffer the records before writing them in the database. This instruction is used rather than the instructionWrite, in a processing to massively create records in a same table.
If the variable [S]adxwrb <= 1, the Writeb instruction works like the Write instruction.
If variable [S]adxwrb > 1, the Writeb instruction will buffer the record. The buffered records are then written in the database at the nth call of the Writeb instruction, 'n' being the value of the system variable [s]adxwrb.
Writeb on a table including a clob and where a blob behaves like a Write, the record will not be buffered.
Writeb updates the variable fstat to indicate how the writing has been carried out. By contrast, the erroneous line is not identified. A solution to identify the erroneous line is to position [S]adxwrb after the rollback and to try again the update transaction without buffering. Thus, the error will occur on the pending line.
fstat | Code | Ecr. | Explanation |
0 | Yes | Writing has been carried out correctly. | |
1 | ERLOCK | No | Locked table. |
3 | ERDUPCL | No | Trying to create a duplicate key. |
Instruction Flush triggers the write of lines that would not have been written and would remain in the buffer. Caution, it is imperative to carry out a Flush before carrying out the Commit! In fact, not only the Commit does not "flush" the lines sent by Writeb, but it generates an error if some lines remain in the writing buffer, unsent. Therefore, it is important to carry out a Flush to make sure that the lines have been written and, in all cases, before carrying out the final Commit.
Instruction Rollback takes into account, of course, all the writing, whether it is carried out via Writeb orWrite.
The example, hereafter, shows a case where cascade creations are carried out. This is the most interesting case as far as optimizing is concerned. The purpose of the code written on the label ROL_TR_1 is not to start the transaction over again with the hope that it could succeed (if there were duplicate keys, they will remain, except if they are deleted by another user); the purpose is to simply relaunch the transaction, line by line, so as to know how to identify the exact line where the error occurs.
The modification of variable adxwrb leads to an error if the lines are still in writing buffers. Therefore it is imperative to carry out the Flush or the Rollback before modifying it.
Since instruction Writeb affects buffers whose size is bigger than the usual writing buffers, an error "not enough memory" (31) might occur when it is executed. In this case, parameter sadmem should be upgraded. This parameter adjusts the memory consumed by the sadloc and sadora processings. By default, on a 6.2 standard version, it is set to 20480. If necessary, increase the value according to the number of tables in which the buffer writing is carried out, the size of a recording of these tables and the value given toadxwrb.
Caution!
Processings should be written in the following way, so as to avoid problems with the order in which records are written in the database. If the record writing order is important, instruction Writeb should not be used with an Update or a Write instruction.
Exemple 1 - incorrect
adxwrb=10 : [F:ABC]CLE= "ABC" [F:ABC]NOM=”alphabet”: Writeb [ABC]
Update [F:ABC]CLE=”ABC” With NOM=”alpha plus bête qu’avant” : Writeb [ABC]
In this example, an error will occur on the Update because the key is not really written yet.
Example 2 - incorrect
adxwrb=1+int(rnd(10))
Trbegin [ABC]
For I=1 to 100
Read [F:ABC]CLE
[F:ABC]CLE= "ABC"+num$(I) : Writeb [ABC]
[F:ABC]CLE= "DEF"+num$(I) : Write [ABC]
Next I
Flush [ABC]
Commit
At the end of the loop, lines "ABC" and "DEF" will be inserted in the database, but there is no control on the order in which they are inserted (lines EDF are inserted progressively, lines ABC are inserted by packets of adxwrb lines).