RE: CDC API - Change database
Larry asked whether Informix's CDC API is free or a purchased add-on, and how to use it. Answers: the CDC API ships with Informix at no charge (InfoSphere/DataMirror CDC is the separate chargeable product). It isn't active by default — you run $INFORMIXDIR/etc/syscdcv1.sql to create the syscdcv1 database and its UDRs, then call procedures to enable full logging and register tables, reading changes from a pseudo smart blob. It only captures changes; you must write a long-running C program to apply them, or use InfoSphere CDC, Enterprise Replication, or third-party tools (DBMoto, Sybase RepServer, Infotrace). The PDF CDC API Programmer's Guide (SC27-3527-00), with sample code, was recommended over the InfoCenter docs.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have had a lot of good responseses from my previous query. The only problem that I am having now is the following: From one source, I am told that the CDC API is not included with Informix and is an item requiring purchase (Software vendor). From another source I am told that this is a free item (tech support). I am not sure which is correct, nor do I still know where I can get the software for the API. Can anyone provide clarification on this? Also, is this CDC API that same as the IBM product, Infocenter CDC; or are they two different products? Larry
OK: CDC API is free and included with Informix. There is a manual for how to use it included in the manual set. InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL tool that uses the CDC API. This is the chargeable product. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > I have had a lot of good responseses from my previous query. The only > problem > that I am having now is the following: > > >From one source, I am told that the CDC API is not included with Informix > and > is an item requiring purchase (Software vendor). > > >From another source I am told that this is a free item (tech support). > > I am not sure which is correct, nor do I still know where I can get the > software for the API. Can anyone provide clarification on this? > > Also, is this CDC API that same as the IBM product, Infocenter CDC; or are > they two different products? > > Larry > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec52994bb61579604ac5a5977
Thank you for the clarification. Do I need to install it, or is it already built in and I just use it? Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: CDC API - Change database [24872] > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > OK: CDC API is free and included with Informix. There is a manual for how > to use it included in the manual set. > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > tool that uses the CDC API. This is the chargeable product. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > I have had a lot of good responseses from my previous query. The only > > problem > > that I am having now is the following: > > > > >From one source, I am told that the CDC API is not included with Informix > > and > > is an item requiring purchase (Software vendor). > > > > >From another source I am told that this is a free item (tech support). > > > > I am not sure which is correct, nor do I still know where I can get the > > software for the API. Can anyone provide clarification on this? > > > > Also, is this CDC API that same as the IBM product, Infocenter CDC; or are > > they two different products? > > > > Larry > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec52994bb61579604ac5a5977 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Built-in but you need to initialize it by creating the syscdcv1 database and installing the stored procedures that implement the functionality. There is a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. See the Change Data Capture Programmer's Guide for details. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > Thank you for the clarification. Do I need to install it, or is it already > built in and I just use it? > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: CDC API - Change database [24872] > > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > > > OK: CDC API is free and included with Informix. There is a manual for how > > to use it included in the manual set. > > > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > > tool that uses the CDC API. This is the chargeable product. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > I have had a lot of good responseses from my previous query. The only > > > problem > > > that I am having now is the following: > > > > > > >From one source, I am told that the CDC API is not included with > Informix > > > and > > > is an item requiring purchase (Software vendor). > > > > > > >From another source I am told that this is a free item (tech support). > > > > > > I am not sure which is correct, nor do I still know where I can get the > > > software for the API. Can anyone provide clarification on this? > > > > > > Also, is this CDC API that same as the IBM product, Infocenter CDC; or > are > > > they two different products? > > > > > > Larry > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --bcaec52994bb61579604ac5a5977 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e844092372204ac5baf59
Is there any better documentation on the CDDC API? I have looked in the Information Center documentation and it does not explain much. It says to run the syscdcv1.sql script to do the initial setup, but then it gets fuzzy. 1) It mentions accessing the data using C programs that I develop. It does really explain how the data gets into the new database. It hints at creating a program to capture the data and put it into the new database, but I thought that was the CDC API's job. It appears that I use a tool like DBAccess to run sql to set up the change monitoring. Is that true, and does the data appear automatically in the new database after that? 2) Can't I just access the data like I would a regular database? It is unclear what the table names will be for the data. Is it the same name as the table in the original database where the changes were made? It is frustrating that IBM/Informix provides a tool like the CDC API, but has very little documentation on how to use it if you are wanting to use it for the first time. Does anyone else have any experience with the CDC API? I can't even find enough information to make an informed decision on whether I can use if for my needs or not. Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: CDC API - Change database [24877] > Date: Wed, 7 Sep 2011 11:34:58 -0400 > > Built-in but you need to initialize it by creating the syscdcv1 database and > installing the stored procedures that implement the functionality. There is > a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. See > the Change Data Capture Programmer's Guide for details. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Thank you for the clarification. Do I need to install it, or is it already > > built in and I just use it? > > > > Larry > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: CDC API - Change database [24872] > > > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > > > > > OK: CDC API is free and included with Informix. There is a manual for how > > > to use it included in the manual set. > > > > > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > > > tool that uses the CDC API. This is the chargeable product. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > wrote: > > > > > > > I have had a lot of good responseses from my previous query. The only > > > > problem > > > > that I am having now is the following: > > > > > > > > >From one source, I am told that the CDC API is not included with > > Informix > > > > and > > > > is an item requiring purchase (Software vendor). > > > > > > > > >From another source I am told that this is a free item (tech support). > > > > > > > > I am not sure which is correct, nor do I still know where I can get the > > > > software for the API. Can anyone provide clarification on this? > > > > > > > > Also, is this CDC API that same as the IBM product, Infocenter CDC; or > > are > > > > they two different products? > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --bcaec52994bb61579604ac5a5977 > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba6e844092372204ac5baf59 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
The CDC API is API for Change Data Capture. I think that you might be confusing the API with Infosphere Change Data Capture. The CDC API provides the means of capturing the changes made to a database. It is not data replication The CDC API is an API which provides the ability to snoop the logical logs to capture the changes in the database. It does not apply those changes to a target. If you want to apply the changes to a different database, you would need to use Infosphere Change Data Capture (i.e. DataMirror). or write your own capture/apply program. From: "LARRY SORENSEN" <lsorensen25@msn.com> To: ids@iiug.org Date: 09/12/2011 08:38 AM Subject: RE: CDC API - Change database [24887] Sent by: ids-bounces@iiug.org Is there any better documentation on the CDDC API? I have looked in the Information Center documentation and it does not explain much. It says to run the syscdcv1.sql script to do the initial setup, but then it gets fuzzy. 1) It mentions accessing the data using C programs that I develop. It does really explain how the data gets into the new database. It hints at creating a program to capture the data and put it into the new database, but I thought that was the CDC API's job. It appears that I use a tool like DBAccess to run sql to set up the change monitoring. Is that true, and does the data appear automatically in the new database after that? 2) Can't I just access the data like I would a regular database? It is unclear what the table names will be for the data. Is it the same name as the table in the original database where the changes were made? It is frustrating that IBM/Informix provides a tool like the CDC API, but has very little documentation on how to use it if you are wanting to use it for the first time. Does anyone else have any experience with the CDC API? I can't even find enough information to make an informed decision on whether I can use if for my needs or not. Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: CDC API - Change database [24877] > Date: Wed, 7 Sep 2011 11:34:58 -0400 > > Built-in but you need to initialize it by creating the syscdcv1 database and > installing the stored procedures that implement the functionality. There is > a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. See > the Change Data Capture Programmer's Guide for details. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > Thank you for the clarification. Do I need to install it, or is it already > > built in and I just use it? > > > > Larry > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: CDC API - Change database [24872] > > > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > > > > > OK: CDC API is free and included with Informix. There is a manual for how > > > to use it included in the manual set. > > > > > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > > > tool that uses the CDC API. This is the chargeable product. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > wrote: > > > > > > > I have had a lot of good responseses from my previous query. The only > > > > problem > > > > that I am having now is the following: > > > > > > > > >From one source, I am told that the CDC API is not included with > > Informix > > > > and > > > > is an item requiring purchase (Software vendor). > > > > > > > > >From another source I am told that this is a free item (tech support). > > > > > > > > I am not sure which is correct, nor do I still know where I can get the > > > > software for the API. Can anyone provide clarification on this? > > > > > > > > Also, is this CDC API that same as the IBM product, Infocenter CDC; or > > are > > > > they two different products? > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --bcaec52994bb61579604ac5a5977 > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba6e844092372204ac5baf59 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
I am looking for something that just provides that changes to the tables that I select. Is the CDC API a manual process. I was under the assumption that it was something that you could turn on and then it was continuous. What it is sounding like is.... I need to write a C program. Somehow, I run that program occassionally against the log files to capture the changes that occurred in the tables that I am watching. Is that somewhat accurate? If so, why is that sql script needed to set things up if everything is just manual and the change data does not end up in a database? Is there a book or something that explains the process a little better than the InfoCenter does? Larry > To: ids@iiug.org > From: mpruet@us.ibm.com > Subject: RE: CDC API - Change database [24888] > Date: Mon, 12 Sep 2011 09:56:20 -0400 > > The CDC API is API for Change Data Capture. I think that you might be > confusing the API with Infosphere Change Data Capture. The CDC API > provides the means of capturing the changes made to a database. It is not > data replication > > The CDC API is an API which provides the ability to snoop the logical logs > to capture the changes in the database. It does not apply those changes to > a target. If you want to apply the changes to a different database, you > would need to use Infosphere Change Data Capture (i.e. DataMirror). or > write your own capture/apply program. > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > To: ids@iiug.org > > Date: 09/12/2011 08:38 AM > > Subject: RE: CDC API - Change database [24887] > > Sent by: ids-bounces@iiug.org > > Is there any better documentation on the CDDC API? I have looked in the > Information Center documentation and it does not explain much. It says to > run > the syscdcv1.sql script to do the initial setup, but then it gets fuzzy. > > 1) It mentions accessing the data using C programs that I develop. It does > really explain how the data gets into the new database. It hints at > creating a > program to capture the data and put it into the new database, but I thought > > that was the CDC API's job. It appears that I use a tool like DBAccess to > run > sql to set up the change monitoring. Is that true, and does the data appear > > automatically in the new database after that? > > 2) Can't I just access the data like I would a regular database? It is > unclear > what the table names will be for the data. Is it the same name as the table > in > the original database where the changes were made? > > It is frustrating that IBM/Informix provides a tool like the CDC API, but > has > very little documentation on how to use it if you are wanting to use it for > > the first time. Does anyone else have any experience with the CDC API? I > can't > even find enough information to make an informed decision on whether I can > use > if for my needs or not. > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: CDC API - Change database [24877] > > Date: Wed, 7 Sep 2011 11:34:58 -0400 > > > > Built-in but you need to initialize it by creating the syscdcv1 database > and > > installing the stored procedures that implement the functionality. There > is > > a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. See > > > the Change Data Capture Programmer's Guide for details. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > Thank you for the clarification. Do I need to install it, or is it > already > > > built in and I just use it? > > > > > > Larry > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: CDC API - Change database [24872] > > > > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > > > > > > > OK: CDC API is free and included with Informix. There is a manual for > > how > > > > to use it included in the manual set. > > > > > > > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > > > > tool that uses the CDC API. This is the chargeable product. > > > > > > > > Art > > > > > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > > wrote: > > > > > > > > > I have had a lot of good responseses from my previous query. The > only > > > > > problem > > > > > that I am having now is the following: > > > > > > > > > > >From one source, I am told that the CDC API is not included with > > > Informix > > > > > and > > > > > is an item requiring purchase (Software vendor). > > > > > > > > > > >From another source I am told that this is a free item (tech > support). > > > > > > > > > > I am not sure which is correct, nor do I still know where I can get > > the > > > > > software for the API. Can anyone provide clarification on this? > > > > > > > > > > Also, is this CDC API that same as the IBM product, Infocenter CDC; > or > > > are > > > > > they two different products? > > > > > > > > > > Larry > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > --bcaec52994bb61579604ac5a5977 > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --90
The CDC API is used if you are going to a non-Informix database. If you are planning on replicating between two Informix databases, you should use ER. The CDC API is using basic SQL commands to capture the log changes. The SQL script is used to create a database and the hooks into the internal code which make up the API. This is done by creating several UDRs which interface with the logs. You obtain the changed data from the logs by 1) executing a procedure in that database to turn on full logging for the requested table and then 2) specifying what table you want to capture. Once you activate the capture, then you receive the log changes from a pseudo smart blob. These interfaces are all provided by the SQL script that you need to run to enable the CDC API. I'd suggest reading the CDC API manual (SC27-3527-00) M.P. From: "LARRY SORENSEN" <lsorensen25@msn.com> To: ids@iiug.org Date: 09/12/2011 09:06 AM Subject: RE: CDC API - Change database [24889] Sent by: ids-bounces@iiug.org I am looking for something that just provides that changes to the tables that I select. Is the CDC API a manual process. I was under the assumption that it was something that you could turn on and then it was continuous. What it is sounding like is.... I need to write a C program. Somehow, I run that program occassionally against the log files to capture the changes that occurred in the tables that I am watching. Is that somewhat accurate? If so, why is that sql script needed to set things up if everything is just manual and the change data does not end up in a database? Is there a book or something that explains the process a little better than the InfoCenter does? Larry > To: ids@iiug.org > From: mpruet@us.ibm.com > Subject: RE: CDC API - Change database [24888] > Date: Mon, 12 Sep 2011 09:56:20 -0400 > > The CDC API is API for Change Data Capture. I think that you might be > confusing the API with Infosphere Change Data Capture. The CDC API > provides the means of capturing the changes made to a database. It is not > data replication > > The CDC API is an API which provides the ability to snoop the logical logs > to capture the changes in the database. It does not apply those changes to > a target. If you want to apply the changes to a different database, you > would need to use Infosphere Change Data Capture (i.e. DataMirror). or > write your own capture/apply program. > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > To: ids@iiug.org > > Date: 09/12/2011 08:38 AM > > Subject: RE: CDC API - Change database [24887] > > Sent by: ids-bounces@iiug.org > > Is there any better documentation on the CDDC API? I have looked in the > Information Center documentation and it does not explain much. It says to > run > the syscdcv1.sql script to do the initial setup, but then it gets fuzzy. > > 1) It mentions accessing the data using C programs that I develop. It does > really explain how the data gets into the new database. It hints at > creating a > program to capture the data and put it into the new database, but I thought > > that was the CDC API's job. It appears that I use a tool like DBAccess to > run > sql to set up the change monitoring. Is that true, and does the data appear > > automatically in the new database after that? > > 2) Can't I just access the data like I would a regular database? It is > unclear > what the table names will be for the data. Is it the same name as the table > in > the original database where the changes were made? > > It is frustrating that IBM/Informix provides a tool like the CDC API, but > has > very little documentation on how to use it if you are wanting to use it for > > the first time. Does anyone else have any experience with the CDC API? I > can't > even find enough information to make an informed decision on whether I can > use > if for my needs or not. > > Larry > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: CDC API - Change database [24877] > > Date: Wed, 7 Sep 2011 11:34:58 -0400 > > > > Built-in but you need to initialize it by creating the syscdcv1 database > and > > installing the stored procedures that implement the functionality. There > is > > a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. See > > > the Change Data Capture Programmer's Guide for details. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> > wrote: > > > > > Thank you for the clarification. Do I need to install it, or is it > already > > > built in and I just use it? > > > > > > Larry > > > > > > > To: ids@iiug.org > > > > From: art.kagel@gmail.com > > > > Subject: Re: CDC API - Change database [24872] > > > > Date: Wed, 7 Sep 2011 09:59:17 -0400 > > > > > > > > OK: CDC API is free and included with Informix. There is a manual for > > how > > > > to use it included in the manual set. > > > > > > > > InfoCenter CDC is a separate product, a kind of intra-RDBMS ETL > > > > tool that uses the CDC API. This is the chargeable product. > > > > > > > > Art > > > > > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 9:47 AM, LARRY SORENSEN <lsorensen25@msn.com> > > > wrote: > > > > > > > > > I have had a lot of good responseses from my previous query. The > only > > > > > problem > > > > > that I am having now is the following: > > > > > > > > > > >From one source, I am told that the CDC API is not included with > > > Informix > > > > > and > > > > > is an item requiring purchase (Software vendor). > > > > > > > > > > >From another source I am told that this is a free item (tech > support). > > > > > > > > > > I am not sure which is correct, nor do I st
Larry: The SQL script installs the UDRs that are needed by the CDC API in the database. It just initializes things so that the API will work. By default it is not installed. If you do not want to write the capture yourself, there are several options: - Infosphere Change Data Capture which is a package that lets you treat the CDC data as an ETL/ELT data stream and do whatever you want with it. You can use it to populate another database, you can output change audit records, etc. - There are 3rd party data replication packages that use the CDC API. Among them: - DBMoto v7.1 will replicate between servers from Informix, Sybase, Oracle, DB2, etc. using CDC where it is available and triggers where it is not. It does support the Informix CDC API (earlier releases used triggers for Informix data sources). See: http://www.hitsw.com/products_services/dbmoto/dbmoto.html - Sybase's Replication Server has options to replicate between Informix and Sybase bydirectionally. See http://www.sybase.com/detail?id=1019236 - Infosphere CDC (of course) - There are other ways to capture changes to your data. - There is a product called Infotrace (see www.lintel.co.uk/infotrace) basically uses the transactions logs and live system to re-construct changes that have been made to selected database tables(s) and records the results in another database which has a Java front end for query purposes. This is the best solution (besides doing it yourself using the CDC API) if you want a record in another database of the actual changes, who changed the data, and when they changes were made rather than an up-to-date copy of the data. - As Madison suggests, you can use Enterprise Replication to capture changes to your data and maintain another copy of it up-to-date. On your comments on the CDC API and the documentation, see my notes below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Sep 12, 2011 at 10:04 AM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > I am looking for something that just provides that changes to the tables > that > I select. Is the CDC API a manual process. I was under the assumption that > it > was something that you could turn on and then it was continuous. > > What it is sounding like is.... > > I need to write a C program. Somehow, I run that program occassionally > against > the log files to capture the changes that occurred in the tables that I am > watching. Is that somewhat accurate? > Actually, you would write a C program that would start whenever the Informix server is restarted and just run all the time. Once it registers itself for the table(s) that it wants to monitor, it will continue to receive data as long as the server is online. The sample program in the CDC API manual exits when it has drained the queue, but there's no reason that there could not be an outer loop that sleeps for a while and goes back into the inner loop to get more data. > > If so, why is that sql script needed to set things up if everything is just > manual and the change data does not end up in a database? > See above and Madison's explanation for the script. As to putting the data into a database, once you have the log records in your CDC API application, you can do whatever you like with them. Print out a running text log, write the log records to a database, use the information contained in them to update a copy of the original tables in another database - even in a server from a different vendor or one that does not support SQL, etc. > > Is there a book or something that explains the process a little better than > the InfoCenter does? > The InfoCenter versions of the manuals are just AWEFUL! You can download the full PDF manual set or individual manuals from the IBM web site and they are MUCH better. The CDC API Programmer's Manual is very detailed and even contains a sample program (IB that the source of the same is in the --90e6ba21219b1bca9804acc15a9f
OK. I just read the manual. Let me see if I have the flow correctly.. 1) Run the sql script to set up the CDC database (The database and CDC now lays dormant until you write a program that opens a sessions and starts reading the logs. 2) Write the program to connect to the CDC database and start processing transaction logs. This can run continuously until stopped or run occassionally. 3) No actual changed data is stored in the CDC database. It is only metadata on where you are in the log records you have been processing. 4) The program itself determines which tables to access change data on when reading the transaction logs. Table information is not stored in the CDC database? (This part is unclear to me. If my target database is already logging, is it the CDC database that I am turning on logging for my table or are you just making sure that my target database is logging? Does the CDC database actually hold any of my data that I am trying to capture or is it as I said, just metadata. I am sorry about all of these questions. I am still trying to decide what amount of effort will be required for this and what the CDC API actually does for me automatically. Larry > To: ids@iiug.org > From: mpruet@us.ibm.com > Subject: RE: CDC API - Change database [24890] > Date: Mon, 12 Sep 2011 10:23:17 -0400 > > The CDC API is used if you are going to a non-Informix database. If you > are planning on replicating between two Informix databases, you should use > ER. > > The CDC API is using basic SQL commands to capture the log changes. The > SQL script is used to create a database and the hooks into the internal > code which make up the API. This is done by creating several UDRs which > interface with the logs. You obtain the changed data from the logs by 1) > executing a procedure in that database to turn on full logging for the > requested table and then 2) specifying what table you want to capture. > Once you activate the capture, then you receive the log changes from a > pseudo smart blob. These interfaces are all provided by the SQL script > that you need to run to enable the CDC API. > > I'd suggest reading the CDC API manual (SC27-3527-00) > > M.P. > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > To: ids@iiug.org > > Date: 09/12/2011 09:06 AM > > Subject: RE: CDC API - Change database [24889] > > Sent by: ids-bounces@iiug.org > > I am looking for something that just provides that changes to the tables > that > I select. Is the CDC API a manual process. I was under the assumption that > it > was something that you could turn on and then it was continuous. > > What it is sounding like is.... > > I need to write a C program. Somehow, I run that program occassionally > against > the log files to capture the changes that occurred in the tables that I am > watching. Is that somewhat accurate? > > If so, why is that sql script needed to set things up if everything is just > > manual and the change data does not end up in a database? > > Is there a book or something that explains the process a little better than > > the InfoCenter does? > > Larry > > > To: ids@iiug.org > > From: mpruet@us.ibm.com > > Subject: RE: CDC API - Change database [24888] > > Date: Mon, 12 Sep 2011 09:56:20 -0400 > > > > The CDC API is API for Change Data Capture. I think that you might be > > confusing the API with Infosphere Change Data Capture. The CDC API > > provides the means of capturing the changes made to a database. It is not > > > data replication > > > > The CDC API is an API which provides the ability to snoop the logical > logs > > to capture the changes in the database. It does not apply those changes > to > > a target. If you want to apply the changes to a different database, you > > would need to use Infosphere Change Data Capture (i.e. DataMirror). or > > write your own capture/apply program. > > > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > > > To: ids@iiug.org > > > > Date: 09/12/2011 08:38 AM > > > > Subject: RE: CDC API - Change database [24887] > > > > Sent by: ids-bounces@iiug.org > > > > Is there any better documentation on the CDDC API? I have looked in the > > Information Center documentation and it does not explain much. It says to > > > run > > the syscdcv1.sql script to do the initial setup, but then it gets fuzzy. > > > > 1) It mentions accessing the data using C programs that I develop. It > does > > really explain how the data gets into the new database. It hints at > > creating a > > program to capture the data and put it into the new database, but I > thought > > > > that was the CDC API's job. It appears that I use a tool like DBAccess to > > > run > > sql to set up the change monitoring. Is that true, and does the data > appear > > > > automatically in the new database after that? > > > > 2) Can't I just access the data like I would a regular database? It is > > unclear > > what the table names will be for the data. Is it the same name as the > table > > in > > the original database where the changes were made? > > > > It is frustrating that IBM/Informix provides a tool like the CDC API, but > > > has > > very little documentation on how to use it if you are wanting to use it > for > > > > the first time. Does anyone else have any experience with the CDC API? I > > can't > > even find enough information to make an informed decision on whether I > can > > use > > if for my needs or not. > > > > Larry > > > > > To: ids@iiug.org > > > From: art.kagel@gmail.com > > > Subject: Re: CDC API - Change database [24877] > > > Date: Wed, 7 Sep 2011 11:34:58 -0400 > > > > > > Built-in but you need to initialize it by creating the syscdcv1 > database > > and > > > installing the stored procedures that implement the functionality. > There > > is > > > a script, $INFORMIXDIR/etc/syscdcv1.sql, that you execute to do that. > See > > > > > the Change Data Capture Programmer's Guide for details. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Sep 7, 2011 at 11:20 AM, LARRY SORENSEN <lsorensen25@msn.com> > > wrote: > > > > > > > Thank you for the clarification. Do I need to install it, or is it > > already > > > > built in and I just use it? > > > > > > > > Larry > > > > > > > > > To: ids@iiug.org > > > > > From: art.kagel@gmail.com > > > > > Subject: Re: CDC API - Change database [24872] > > > > > Date: Wed, 7 Sep 2
See below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Sep 12, 2011 at 1:00 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > OK. I just read the manual. Let me see if I have the flow correctly.. > > 1) Run the sql script to set up the CDC database (The database and CDC now > lays dormant until you write a program that opens a sessions and starts > reading the logs. > Yes > 2) Write the program to connect to the CDC database and start processing > transaction logs. This can run continuously until stopped or run > occassionally. > Yes > 3) No actual changed data is stored in the CDC database. It is only > metadata > on where you are in the log records you have been processing. > Yes > 4) The program itself determines which tables to access change data on when > reading the transaction logs. Table information is not stored in the CDC > Correct. > database? (This part is unclear to me. If my target database is already > logging, is it the CDC database that I am turning on logging for my table > or > are you just making sure that my target database is logging? Does the CDC > database actually hold any of my data that I am trying to capture or is it > as > I said, just metadata. > The main thing that the cdc database holds that is user usable, is descriptions of error codes and log record types. See the description of the syscdcerrcodes and syscdcrectypes tables > > I am sorry about all of these questions. I am still trying to decide what > amount of effort will be required for this and what the CDC API actually > does > for me automatically. > Not much. It is an API used for doing things yourself. If you want automatic replication look to Enterprise Replication, or MACH11 secondaries. If you want automatic change auditing, look to the Informix Secure Audit Facility. Or purchase one of the IBM or 3rd party tools I mentioned (and others) that will use the API to automate these things for you. > Larry > > > To: ids@iiug.org > > From: mpruet@us.ibm.com > > Subject: RE: CDC API - Change database [24890] > > Date: Mon, 12 Sep 2011 10:23:17 -0400 > > > > The CDC API is used if you are going to a non-Informix database. If you > > are planning on replicating between two Informix databases, you should > use > > ER. > > > > The CDC API is using basic SQL commands to capture the log changes. The > > SQL script is used to create a database and the hooks into the internal > > code which make up the API. This is done by creating several UDRs which > > interface with the logs. You obtain the changed data from the logs by 1) > > executing a procedure in that database to turn on full logging for the > > requested table and then 2) specifying what table you want to capture. > > Once you activate the capture, then you receive the log changes from a > > pseudo smart blob. These interfaces are all provided by the SQL script > > that you need to run to enable the CDC API. > > > > I'd suggest reading the CDC API manual (SC27-3527-00) > > > > M.P. > > > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > > > To: ids@iiug.org > > > > Date: 09/12/2011 09:06 AM > > > > Subject: RE: CDC API - Change database [24889] > > > > Sent by: ids-bounces@iiug.org > > > > I am looking for something that just provides that changes to the tables > > that > > I select. Is the CDC API a manual process. I was under the assumption > that > > it > > was something that you could turn on and then it was continuous. > > > > What it is sounding like is.... > > > > I need to write a C program. Somehow, I run that program occassionally > > against > > the log files to capture the changes that occurred in the tables that I > am > > watching. Is that somewhat accurate? > > > > If so, why is that sql script needed to set things up if everything is > just > > > > manual and the change data does not end up in a database? > > > > Is there a book or something that explains the process a little better > than > > > > the InfoCenter does? > > > > Larry > > > > > To: ids@iiug.org > > > From: mpruet@us.ibm.com > > > Subject: RE: CDC API - Change database [24888] > > > Date: Mon, 12 Sep 2011 09:56:20 -0400 > > > > > > The CDC API is API for Change Data Capture. I think that you might be > > > confusing the API with Infosphere Change Data Capture. The CDC API > > > provides the means of capturing the changes made to a database. It is > not > > > > > data replication > > > > > > The CDC API is an API which provides the ability to snoop the logical > > logs > > > to capture the changes in the database. It does not apply those changes > > to > > > a target. If you want to apply the changes to a different database, you > > > would need to use Infosphere Change Data Capture (i.e. DataMirror). or > > > write your own capture/apply program. > > > > > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > > > > > To: ids@iiug.org > > > > > > Date: 09/12/2011 08:38 AM > > > > > > Subject: RE: CDC API - Change database [24887] > > > > > > Sent by: ids-bounces@iiug.org > > > > > > Is there any better documentation on the CDDC API? I have looked in the > > > Information Center documentation and it does not explain much. It says > to > > > > > run > > > the syscdcv1.sql script to do the initial setup, but then it gets > fuzzy. > > > > > > 1) It mentions accessing the data using C programs that I develop. It > > does > > > really explain how the data gets into the new database. It hints at > > > creating a > > > program to capture the data and put it into the new database, but I > > thought > > > > > > that was the CDC API's job. It appears that I use a tool like DBAccess > to > > > > > run > > > sql to set up the change monitoring. Is that true, and does the data > > appear > > > > > > automatically in the new database after that? > > > > > > 2) Can't I just access the data like I would a regular database? It is > > > unclear > > > what the table names will be for the data. Is it the same name as the > > table > > > in > > > the original database where the changes were made? > > > > > > It is frustrating that IBM/Informix provides a tool like the CDC API, > but > > > > > has > > > very little documentation on how to use it if you are wanting to use it > > for > > > > > > the first time. Does anyone else have any experience with the CDC API? > I > > > can't > > > even find enough information to make an informed decision on whether I > > can > > > use@@N
Thank you to Art and Madison for their indulgence and exhaustive explanation. I think it is finally starting to clear up for me. IBM just does not have a very good explanation/description for any of this. Larry > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: CDC API - Change database [24893] > Date: Mon, 12 Sep 2011 13:14:22 -0400 > > See below: > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Sep 12, 2011 at 1:00 PM, LARRY SORENSEN <lsorensen25@msn.com> wrote: > > > OK. I just read the manual. Let me see if I have the flow correctly.. > > > > 1) Run the sql script to set up the CDC database (The database and CDC now > > lays dormant until you write a program that opens a sessions and starts > > reading the logs. > > > > Yes > > > 2) Write the program to connect to the CDC database and start processing > > transaction logs. This can run continuously until stopped or run > > occassionally. > > > Yes > > > 3) No actual changed data is stored in the CDC database. It is only > > metadata > > on where you are in the log records you have been processing. > > > Yes > > > 4) The program itself determines which tables to access change data on when > > reading the transaction logs. Table information is not stored in the CDC > > > Correct. > > > database? (This part is unclear to me. If my target database is already > > logging, is it the CDC database that I am turning on logging for my table > > or > > are you just making sure that my target database is logging? Does the CDC > > database actually hold any of my data that I am trying to capture or is it > > as > > I said, just metadata. > > > The main thing that the cdc database holds that is user usable, is > descriptions of error codes and log record types. See the description of > the syscdcerrcodes and syscdcrectypes tables > > > > > I am sorry about all of these questions. I am still trying to decide what > > amount of effort will be required for this and what the CDC API actually > > does > > for me automatically. > > > Not much. It is an API used for doing things yourself. If you want > automatic replication look to Enterprise Replication, or MACH11 > secondaries. If you want automatic change auditing, look to the Informix > Secure Audit Facility. Or purchase one of the IBM or 3rd party tools I > mentioned (and others) that will use the API to automate these things for > you. > > > Larry > > > > > To: ids@iiug.org > > > From: mpruet@us.ibm.com > > > Subject: RE: CDC API - Change database [24890] > > > Date: Mon, 12 Sep 2011 10:23:17 -0400 > > > > > > The CDC API is used if you are going to a non-Informix database. If you > > > are planning on replicating between two Informix databases, you should > > use > > > ER. > > > > > > The CDC API is using basic SQL commands to capture the log changes. The > > > SQL script is used to create a database and the hooks into the internal > > > code which make up the API. This is done by creating several UDRs which > > > interface with the logs. You obtain the changed data from the logs by 1) > > > executing a procedure in that database to turn on full logging for the > > > requested table and then 2) specifying what table you want to capture. > > > Once you activate the capture, then you receive the log changes from a > > > pseudo smart blob. These interfaces are all provided by the SQL script > > > that you need to run to enable the CDC API. > > > > > > I'd suggest reading the CDC API manual (SC27-3527-00) > > > > > > M.P. > > > > > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > > > > > To: ids@iiug.org > > > > > > Date: 09/12/2011 09:06 AM > > > > > > Subject: RE: CDC API - Change database [24889] > > > > > > Sent by: ids-bounces@iiug.org > > > > > > I am looking for something that just provides that changes to the tables > > > that > > > I select. Is the CDC API a manual process. I was under the assumption > > that > > > it > > > was something that you could turn on and then it was continuous. > > > > > > What it is sounding like is.... > > > > > > I need to write a C program. Somehow, I run that program occassionally > > > against > > > the log files to capture the changes that occurred in the tables that I > > am > > > watching. Is that somewhat accurate? > > > > > > If so, why is that sql script needed to set things up if everything is > > just > > > > > > manual and the change data does not end up in a database? > > > > > > Is there a book or something that explains the process a little better > > than > > > > > > the InfoCenter does? > > > > > > Larry > > > > > > > To: ids@iiug.org > > > > From: mpruet@us.ibm.com > > > > Subject: RE: CDC API - Change database [24888] > > > > Date: Mon, 12 Sep 2011 09:56:20 -0400 > > > > > > > > The CDC API is API for Change Data Capture. I think that you might be > > > > confusing the API with Infosphere Change Data Capture. The CDC API > > > > provides the means of capturing the changes made to a database. It is > > not > > > > > > > data replication > > > > > > > > The CDC API is an API which provides the ability to snoop the logical > > > logs > > > > to capture the changes in the database. It does not apply those changes > > > to > > > > a target. If you want to apply the changes to a different database, you > > > > would need to use Infosphere Change Data Capture (i.e. DataMirror). or > > > > write your own capture/apply program. > > > > > > > > From: "LARRY SORENSEN" <lsorensen25@msn.com> > > > > > > > > To: ids@iiug.org > > > > > > > > Date: 09/12/2011 08:38 AM > > > > > > > > Subject: RE: CDC API - Change database [24887] > > > > > > > > Sent by: ids-bounces@iiug.org > > > > > > > > Is there any better documentation on the CDDC API? I have looked in the > > > > Information Center documentation and it does not explain much. It says > > to > > > > > > > run > > > > the syscdcv1.sql script to do the initial setup, but then it gets > > fuzzy. > > > > > > > > 1) It mentions accessing the data using C programs that I develop. It > > > does > > > > really explain how the data gets into the new database. It hints at > > > > creating a > > > > program to capture the data and put it into the new database, but I > > > thought > > > > > > > > that was the CDC API's job. It appears that I use a tool like DBAccess > > to > > > > > > > run > > > > sql to set up the change monitoring. Is that true, and does the data > > > appear > > > > > > > > automatically in the n