Data in DB2 tables gets more and more based on text and so-called LOB objects are often seen as columns in DB2 tables. This development also means that we need to perform searches within those texts. For this purpose DB2 has two different functions available: POSITION and POSSTR.

The difference between those two functions is very small and both of them works like INDEX in PLI and POS in REXX. The main difference is the syntax where the two parameters for POSSTR is in the order (haystack, needle), while it is (needle, haystack) for POSITION and POSITION needs a third parameter called string units. This parameter also exists for other more current functions in DB2. As long as your data are stored in good old fashioned EBCDIC format the parameter value OCTETS will be adequate. The other two values for string units are mostly used for data in UNICODE format.

The important difference between POSITION and POSSTR is the needle parameter which in POSITION is allowed to be a column, but this is not allowed in POSSTR. Several times I have had the need to make a kind of join between two tables where I want to find the contents of a column in one table as part of a character string column in another table. Such a join is only possible when using POSITION.