Hi all,
We have an ERP running on Progress 10.2B. We are receiving some width errors when querying the DB via ODBC, so our ERP provider has suggested running DBTool to "fix" the widths of the fields.
I have looked at the Progress KB articles but it's not clear to me what exactly this does. Is it changing the schema of the Progress DB itself, or some SQL-schema overlay?
I have asked our provider if "fixing" the DB using DBTool will have any adverse affect on our ERP data integrity etc, but they are being evasive in giving me a straight response.
I would like to know if any one in the community could advise on the likelihood (I'm not going to hold anyone here accountable!) on data issues should I run this tool?
Also, we have OpenEdge Replication type 3 running on the Live DB. Does anyone know if I run DBTool on our Live DB, will these changes get replicated? I assume, if the changes are just to a separate SQL layer, then they won't, but I am really flying blind here.
Thank you very much.
Best Regards,
Elliot
Hi Elliot, it's kind of changing the schema and it gets reflected on your DF. Specifically it will change the MAX-WIDTH value for some fields.
This MAX-WIDTH thing is part of your DB but for most of the time only the SQL engine cares about it.
I have never seen a problem with changing this value. It's pretty much a requirement if you intent to access your DB through SQL. So I wouldn't worry about data integrity but be sure to have a backup just in case.
As for replication I never tried, but it being a schema thing I bet it gets replicated to your targets.
Regards,
Marcos
Hi Elliot, it's kind of changing the schema and it gets reflected on your DF. Specifically it will change the MAX-WIDTH value for some fields.
This MAX-WIDTH thing is part of your DB but for most of the time only the SQL engine cares about it.
I have never seen a problem with changing this value. It's pretty much a requirement if you intent to access your DB through SQL. So I wouldn't worry about data integrity but be sure to have a backup just in case.
As for replication I never tried, but it being a schema thing I bet it gets replicated to your targets.
Regards,
Marcos
DBTOOL updates the width parameter of a field to be some percentage (user specified) larger than the largest piece of data in the field. If the width of a field is 20 characters and you have a field with 30 characters, the SQL call will fail. Running DBTOOL with a 50% increase will change the width of the field from 20 to 45. This gives some buffer in case the field is overstuffed again. This change only applies to SQL and has no effect on ABL, but it is passed on to the target databases.
In our environment we run DBTOOL on a weekly basis to prevent SQL issues (we use JDBC). We also run JDBC queries against our target database in addition to the source.
In 11.5.1 there is a new feature called Automated Data Truncation that can be turned on to truncate the data being sent to SQL queries to match the current width.
In 11.6 there is the option to increase the width automatically when making a SQL call.
Note that in the Progress schema, fields don't actually have fixed widths. There is a default format for display, but no requirement to use that format for display or populating the field. The original SQL width is a "guess" based on the format, but may be highly inappropriate depending on how the field is used. So, use dbtool regularly to get adequate widths for the actual data. As noted, this width will be preserved if you do a dump/load or similar operation and it will not impact your ABL application.
Absolutely fantastic answers by all. I am much more clear on what this tool is doing now.
We naturally have multiple backups, so I will go ahead and use this tool as required.
Thank you very much!
Best Regards,
Elliot
Note that there is also a configuration parameter that makes the 4GL runtime enforce the maximum field width specified in the field definition. If you use that, then the application is no longer allowed to store values that are longer, instead raising an error in the application.
In the moment I would not use any of these ODBC truncation, autowidth settings whether I asked for them myself.
They seems not to be safe (see alerts in KB about data-loss, wrong data ...)