Hash function in Informix
A user building a Data Vault 2.0 warehouse asked whether Informix offers a built-in hash (MD5 or similar) for generating row keys. Answer: the undocumented built-in ifx_checksum(column, integer) returns a CRC; calls can be nested to checksum several columns (innermost seeded with 0, which affects the result), optionally wrapped in mod(abs(...), n) to bound the range, with character columns cast to LVARCHAR. It's built into the server and used by the cdr check/repair utility. Alternatively, others suggested writing a C or Java hash function as a UDR, with a linked example. Suggestion made to file an RFE to get ifx_checksum documented.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am currently implementing a Datavault 2.0 data wareouse on Informix. The methodology uses hashing of a subset of columns to give a key to new versions of rows. Does Informix have a hashing function (MD5 or other)? If not, is there an easy way to implement it in Informix SPL or some other way? Thanks, Lauri Pietarinen
You can use the ifx_checksum() function. The paraform for the function is integer ifx_checksum(column, integer). This returns a CRC calculation of the column. You can then use the mod function to limit the range of the hash values. You can nest the calls so that you can generate a single CRC/hash value for multiple columns. As an example suppose that you wanted to limit the hash values between 0 and 1000 and wanted to hash on three columns, col1, col2, and col3. Then you could write... LET hash_val = mod(abs(ifx_checksum(col1, ifx_checksum(col2, ifx_checksum(col3, 0))))); or select mod(abs(ifx_checksum(col1, ifx_checksum(col2, ifx_checksum(col3, 0))))) as hash_val from tab1 where.... You need ifx_checksum(col3, 0) as the inner most function. The zero value is just a place holder to start the function. Madison Pruet Retired from IBM On Saturday, November 14, 2015 11:58 AM, LAURI PIETARINEN <lauri.pietarinen@relational-consulting.com> wrote: I am currently implementing a Datavault 2.0 data wareouse on Informix. The methodology uses hashing of a subset of columns to give a key to new versions of rows. Does Informix have a hashing function (MD5 or other)? If not, is there an easy way to implement it in Informix SPL or some other way? Thanks, Lauri Pietarinen ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
It's relatively easy if you can find the code for a functin in C. We allow the implementation of C function that can then be called from SQL. With a bit more time and better Internet acces I could provide some links to examples... On Nov 14, 2015 5:58 PM, "LAURI PIETARINEN" < lauri.pietarinen@relational-consulting.com> wrote: > I am currently implementing a Datavault 2.0 data wareouse on Informix. The > methodology uses hashing of a subset of columns to give a key to new > versions > of rows. > > Does Informix have a hashing function (MD5 or other)? If not, is there an > easy > way to implement it in Informix SPL or some other way? > > Thanks, > Lauri Pietarinen > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b3026b00ce0524872c36
https://justdaveinfo.wordpress.com/2015/04/26/simple-informix-c-udr-on-centos-6- 6/ Regards, David. > On 14 November 2015 at 21:51 Fernando Nunes <domusonline@gmail.com> wrote: > > > It's relatively easy if you can find the code for a functin in C. > We allow the implementation of C function that can then be called from SQL. > With a bit more time and better Internet acces I could provide some links > to examples... > On Nov 14, 2015 5:58 PM, "LAURI PIETARINEN" < > lauri.pietarinen@relational-consulting.com> wrote: > > > I am currently implementing a Datavault 2.0 data wareouse on Informix. The > > methodology uses hashing of a subset of columns to give a key to new > > versions > > of rows. > > > > Does Informix have a hashing function (MD5 or other)? If not, is there an > > easy > > way to implement it in Informix SPL or some other way? > > > > Thanks, > > Lauri Pietarinen > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0158b3026b00ce0524872c36 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Oops, my formula was not quite right.... Try the following instead... mod(abs(ifx_checksum(col1, ifx_checksum(col2, ifx_checksum(col3, 0)))), 1000); Madison Pruet Retired from IBM On Saturday, November 14, 2015 1:29 PM, Madison Pruet <madison_pruet@yahoo.com> wrote: You can use the ifx_checksum() function. The paraform for the function is integer ifx_checksum(column, integer). This returns a CRC calculation of the column. You can then use the mod function to limit the range of the hash values. You can nest the calls so that you can generate a single CRC/hash value for multiple columns. As an example suppose that you wanted to limit the hash values between 0 and 1000 and wanted to hash on three columns, col1, col2, and col3. Then you could write... LET hash_val = mod(abs(ifx_checksum(col1, ifx_checksum(col2, ifx_checksum(col3, 0))))); or select mod(abs(ifx_checksum(col1, ifx_checksum(col2, ifx_checksum(col3, 0))))) as hash_val from tab1 where.... You need ifx_checksum(col3, 0) as the inner most function. The zero value is just a place holder to start the function. Madison Pruet Retired from IBM On Saturday, November 14, 2015 11:58 AM, LAURI PIETARINEN <lauri.pietarinen@relational-consulting.com> wrote: I am currently implementing a Datavault 2.0 data wareouse on Informix. The methodology uses hashing of a subset of columns to give a key to new versions of rows. Does Informix have a hashing function (MD5 or other)? If not, is there an easy way to implement it in Informix SPL or some other way? Thanks, Lauri Pietarinen ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
If you have C or Java code for an appropriate hash function you can easily make that into a UDR (User Defined Routine) in a shared library and map that to an SPL/SQL callable function. Check out the "User-Defined Routines and Data Types Developer's Guide <documentation/ids_udr_bookmap.pdf> " in the Informix PDF manual set. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Nov 14, 2015 at 12:58 PM, LAURI PIETARINEN < lauri.pietarinen@relational-consulting.com> wrote: > I am currently implementing a Datavault 2.0 data wareouse on Informix. The > methodology uses hashing of a subset of columns to give a key to new > versions > of rows. > > Does Informix have a hashing function (MD5 or other)? If not, is there an > easy > way to implement it in Informix SPL or some other way? > > Thanks, > Lauri Pietarinen > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe84eac59ed052489133b
Hi Madison, Thanks for the information. Using ifx_checksum() looks like a great solution for a coding project I am working on. We have legacy code that implements a delete/insert for update operations. There are specific fields I would like to check to determine whether they have changed. It looks like we can store the result from ifx_checksum() and compare the values generated on inserts (through a trigger) to determine if the field has changed or not. Two questions: 1. I did not find this function in the 11.70 or 12.10 documentation. I assume it is ok to use and will be supported. Are there any special considerations I should be aware of? 2. You state the second input parameter is only a place holder for the function and zero is sufficient. Does the second input parameter value affect the output at all? Thanks, Bob Kruse
Well, it's not documented because I was too lazy to document it. It's used by the cdr utility as part of the check/repair, so it's got a lot of usage. You might want to put in a request for change to have it documented in the doc set, however. The second parameter is so that we could generate a single checksum on multiple columns. That way the output of one execution of the ifx_checksum() routine could also be the input of the next. Sort of like chaining the columns together. The innermost call to ifx_checksum needs to have some value as the input value for the second parameter, and I'd just suggest that you use zero. If you use some other value, then the resulting checksum will be different. It will work on all basic data types although, you may need to cast character types to LVARCHAR.... i.e. colname::LVARCHAR... Madison Pruet Retired from IBM On Wednesday, November 18, 2015 1:47 PM, BOB KRUSE <bob.kruse@starwoodhotels.com> wrote: Hi Madison, Thanks for the information. Using ifx_checksum() looks like a great solution for a coding project I am working on. We have legacy code that implements a delete/insert for update operations. There are specific fields I would like to check to determine whether they have changed. It looks like we can store the result from ifx_checksum() and compare the values generated on inserts (through a trigger) to determine if the field has changed or not. Two questions: 1. I did not find this function in the 11.70 or 12.10 documentation. I assume it is ok to use and will be supported. Are there any special considerations I should be aware of? 2. You state the second input parameter is only a place holder for the function and zero is sufficient. Does the second input parameter value affect the output at all? Thanks, Bob Kruse ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Madison, I probably will put in a feature request to have this documented. Thanks for the great information! Very useful!! Bob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Wednesday, November 18, 2015 12:59 PM To: ids@iiug.org Subject: Re: Hash function in Informix [36092] Well, it's not documented because I was too lazy to document it. It's used by the cdr utility as part of the check/repair, so it's got a lot of usage. You might want to put in a request for change to have it documented in the doc set, however. The second parameter is so that we could generate a single checksum on multiple columns. That way the output of one execution of the ifx_checksum() routine could also be the input of the next. Sort of like chaining the columns together. The innermost call to ifx_checksum needs to have some value as the input value for the second parameter, and I'd just suggest that you use zero. If you use some other value, then the resulting checksum will be different. It will work on all basic data types although, you may need to cast character types to LVARCHAR.... i.e. colname::LVARCHAR... Madison Pruet Retired from IBM On Wednesday, November 18, 2015 1:47 PM, BOB KRUSE <bob.kruse@starwoodhotels.com> wrote: Hi Madison, Thanks for the information. Using ifx_checksum() looks like a great solution for a coding project I am working on. We have legacy code that implements a delete/insert for update operations. There are specific fields I would like to check to determine whether they have changed. It looks like we can store the result from ifx_checksum() and compare the values generated on inserts (through a trigger) to determine if the field has changed or not. Two questions: 1. I did not find this function in the 11.70 or 12.10 documentation. I assume it is ok to use and will be supported. Are there any special considerations I should be aware of? 2. You state the second input parameter is only a place holder for the function and zero is sufficient. Does the second input parameter value affect the output at all? Thanks, Bob Kruse ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.
When you write the RFE, mention that the function was originally documented as an external UDR that the customer would have to compile and install. The documentation was available from developer's works and was a requirement to make cdr check work. In version 11.0, we moved the code and installation into the server build to make it easier on the customer base. Too many customers messed up either the compile or the installation. So it's not as if it has never been documented. That might make it easier to get the RFE accepted and formally documented. Madison Pruet Retired from IBM On Thursday, November 19, 2015 4:14 PM, "Kruse, Robert" <bob.kruse@starwoodhotels.com> wrote: Hi Madison, I probably will put in a feature request to have this documented. Thanks for the great information! Very useful!! Bob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Wednesday, November 18, 2015 12:59 PM To: ids@iiug.org Subject: Re: Hash function in Informix [36092] Well, it's not documented because I was too lazy to document it. It's used by the cdr utility as part of the check/repair, so it's got a lot of usage. You might want to put in a request for change to have it documented in the doc set, however. The second parameter is so that we could generate a single checksum on multiple columns. That way the output of one execution of the ifx_checksum() routine could also be the input of the next. Sort of like chaining the columns together. The innermost call to ifx_checksum needs to have some value as the input value for the second parameter, and I'd just suggest that you use zero. If you use some other value, then the resulting checksum will be different. It will work on all basic data types although, you may need to cast character types to LVARCHAR.... i.e. colname::LVARCHAR... Madison Pruet Retired from IBM On Wednesday, November 18, 2015 1:47 PM, BOB KRUSE <bob.kruse@starwoodhotels.com> wrote: Hi Madison, Thanks for the information. Using ifx_checksum() looks like a great solution for a coding project I am working on. We have legacy code that implements a delete/insert for update operations. There are specific fields I would like to check to determine whether they have changed. It looks like we can store the result from ifx_checksum() and compare the values generated on inserts (through a trigger) to determine if the field has changed or not. Two questions: 1. I did not find this function in the 11.70 or 12.10 documentation. I assume it is ok to use and will be supported. Are there any special considerations I should be aware of? 2. You state the second input parameter is only a place holder for the function and zero is sufficient. Does the second input parameter value affect the output at all? Thanks, Bob Kruse ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.