DB2_SetConstraint

Obsolete

The DB2_SetConstraint command was introduced in version 1 of the DataFlex driver for DB2. Since then, new techniques allow the programmer a better and more controllable interface. The command is still available for backward compatibility reasons. For new logic, we recommend the use of the DF_FILE_SQL_FILTER and DF_FILE_SQL_FILTER_ACTIVE attributes or the use of Embedded SQL.

This command was added to give the programmer more control over the SELECT statement generated by the driver. It will effectively turn off the driver logic that generates the WHERE clause of a SELECT statement and replace it with what is passed in the command. Since you are overwriting internal logic, this command has the potential to generate unexpected results when misused. Therefore, the command must be used in a defined way. You must set up the clause, find records in the result set by the index that you want the result ordered by in forward or backward direction (GT, LT), and remove the clause.

For example, if we want to use this functionality in the Order Entry sample to find all order lines for the MODEMS item, we could do this as follows:

Use DB2_DRV
String Clause
Move "ITEM_ID = 'MODEMS'" To Clause
DB2_SetConstraint OrderDtl.File_number Clause
Repeat
    Find Gt OrderDtl By Index.1
    [Found] Showln "Order detail for a modem is: " OrderDtl.Order_Number ", " OrderDtl.Detail_number
Until [Not Found]
DB2_SetConstraint OrderDtl.File_number ""

This feature can speed up reporting in particular. Sometimes looking for related information is more efficient if the WHERE clause is replaced by a ChildColumn = ParentValue construction. The select statements generated by the driver will select the desired record and all that follow according to the index used. By giving it a specific WHERE clause with specific knowledge about the database, you can limit the size of the result sets and thereby increase performance.

The DB2_SetConstraint command will reset the DF_FILE_MAX_ROWS_FETCHED attribute when issued. If the command is issued with a clause, the attribute will be set to zero (0). If it is called with no clause (an empty string), the attribute will be reset to its original value.

Please note that you need to use SQL syntax in the WHERE clause that you specify. If you want to select on dates, you must use the YYYY-MM-DD format that SQL uses. Normally, this is easiest accomplished by:

Date MyDate
Integer OrgFmt OrgSep
Sysdate MyDate
Get_Attribute DF_DATE_FORMAT To OrgFmt
Get_Attribute DF_DATE_SEPARATOR To OrgSep
Set_attribute DF_DATE_FORMAT To DF_DATE_MILITARY
Set_attribute DF_DATE_SEPARATOR To (ASCII("-"))
DB2_SetConstraint MyFile.File_number ("MYDATECOLUMN = '" + string(MyDate) + "'")
Set_attribute DF_DATE_FORMAT To OrgFmt
Set_attribute DF_DATE_SEPARATOR To OrgSep

Also, note that string constants are placed in single quotes (').

OF SPECIAL NOTE

• If this command is used on a table, you cannot use other finds on that table until ending the DB2_SetConstraint. As long as the constraint is in force, it will overwrite the find logic of the driver. If, before ending DB2_SetConstraint, we changed the example above to find on OrderDtl by some other index or in a different direction (not GT) on the same index, the result would be unpredictable.

  Use DB2_DRV
  String Clause
  Move "ITEM_ID = 'MODEMS'" To Clause
  DB2_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: " OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Find Lt OrderDtl By Index.1  // The result of this command is unpredictable
  Until [Not Found]
  DB2_SetConstraint OrderDtl.File_number ""
  

• You cannot change the buffer between finds to re-seed the find logic. Doing that will result in the first record being found again. If we adjust the previous code to the example below, the repeat loop will turn into an endless loop since you are finding the same record repeatedly.

  Use DB2_DRV
  String Clause
  Move "ITEM_ID = 'MODEMS'" To Clause
  DB2_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: " OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Move SomeValue To OrderDtl.Order_number
  Until [Not Found]
  DB2_SetConstraint OrderDtl.File_number ""
  

• You can use find, save, and delete commands on other tables where no DB2_SetConstraint is in force. You could, for example, expand the code above and show the customer names for the customers that bought modems. To achieve this, you would need to find an OrderHeader and a Customer record.

• You can end looping through a DB2_SetConstraint block anytime you like. If we wanted to find only two records in the sample above, we could add a counter, increment it for every found record, and break the loop when the counter reaches two.

  Use DB2_DRV
  String Clause
  Integer Counter
  Move 0 To Counter
  Move "ITEM_ID = 'MODEMS'" To Clause
  DB2_SetConstraint OrderDtl.File_number Clause
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: " ;
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
      Increment Counter
      If (Counter >= 2) Break
  Until [Not Found]
  DB2_SetConstraint OrderDtl.File_number ""
  

• You must use SQL syntax in the string that is passed.

• If you want to write portable applications that support multiple database drivers, you can use the general form of the DB2_SetConstraint command, CLI_SetConstraint. CLI_SetConstraint performs the same functionality. In fact, it is called from the DB2L_SetConstraint command. The CLI_SetConstraint command works exactly as described for DB2_SetConstraint. It gets an extra parameter, the identification of the driver that you want to set the constraint for. If we changed the code above to be more generic, it would look like the sample below.

  Use DB2_DRV
  String Clause
  String DriverId
  Move DB2_DRV_ID To DriverId
  Move "ITEM_ID = 'MODEMS'" To Clause
  CLI_SetConstraint OrderDtl.File_number Clause DriverId
  Repeat
      Find Gt OrderDtl By Index.1
      [Found] Showln "Order detail for a modem is: " ;
      OrderDtl.Order_Number ", " OrderDtl.Detail_number
  Until [Not Found]
  CLI_SetConstraint OrderDtl.File_number "" DriverId
  

DISCLAIMER

Data Access has conducted extensive research into performance issues and design differences between the native DataFlex database and a DB2 database. We found that the DB2_SetConstraint or CLI_SetConstraint command can speed up a connection to a DB2 database considerably. While we are looking into ways to improve internal driver logic to achieve the kind of results these commands can provide, this is not a trivial task and the outcome of further research is uncertain. In the meantime, Data Access wants to offer this solution to the DataFlex community.

You should be aware that the misuse of these commands could yield unexpected results. It is the developer's responsibility to verify that the results are as expected. All risks associated with the use of these commands are the developer's; Data Access Corporation disclaims any and all liability with respect to the use of the DB2_SetConstraint or CLI_SetConstraint commands.