We need to exapnd the number of characters in and Index field for an existing Application Group. Does anyone have information on the best way for doing this?
Thanks,
Russell
I usually update the application group and version it somehow..APPGGROUPa..then copy the app group and name it APPGROUP with the new field length. Then, I set the rname equal to whatever it was set in the previous, map the app grou/folder...boom..done. Not that difficult, but, I'd also like to know if someone else has an easier way!
How would this affect existing data? Wouldn't the current AP Group have to be deleted so the new one can be renamed to the old name and doesn't deleting the app group also delete the data?
Thanks,
the old application group data should stay the same, and if you use the same displayed value name in the field settings they should combine (at least they do with Ondemand web client), and it would be seamless to the user. Best I can say- try it in your pre-production environments and see how you make out.
This also comes in handy for record length changes I've learned.
Hello,
Warning, this is not for the people/company who have weak heart!!!
1. Backup (just to be 100% sure :-D)
2. Stop CMOD
3. do an alter table alter column on each segment related to the application group you want to change. (with the additional length of you varchar)
for for example you want now varchar(200)
ALTER TABLE t1 ALTER COLUMN colnam1 SET DATA TYPE VARCHAR(200)
4. Change the definition on the column definition in CMOD table arsagfld
update arsagfld set string_len=200 where agid=<agid_from_your_application_group> and name='colnam1'
5. Restart CMOD
6. Enjoy the new field feld length with a beer!!! :-)
That's it, quick and functional. I've done it to countless number of customer.
Of course, do it first in a test environment, ensure that it works, and TEST TEST TEST !!!
And only when you are feeling confident, then do it in the production.
Sincerely yours,
Alessandro
To make user I understand this correctly, following would be the way to make the change to increase the column to 24 bytes.
Is this correct?
AGID Table]
5817 MKA1
ALTER TABLE MKA1 ALTER COLUMN CustomerID SET DATA TYPE VARCHAR(24)
update arsagfld set string_len=24 where agid=<5817> and name='CustomerID'
Thanks
Quote from: AlessandroPerucchi on June 14, 2012, 04:56:37 AM
Hello,
Warning, this is not for the people/company who have weak heart!!!
1. Backup (just to be 100% sure :-D)
2. Stop CMOD
3. do an alter table alter column on each segment related to the application group you want to change. (with the additional length of you varchar)
for for example you want now varchar(200)
ALTER TABLE t1 ALTER COLUMN colnam1 SET DATA TYPE VARCHAR(200)
4. Change the definition on the column definition in CMOD table arsagfld
update arsagfld set string_len=200 where agid=<agid_from_your_application_group> and name='colnam1'
5. Restart CMOD
6. Enjoy the new field feld length with a beer!!! :-)
That's it, quick and functional. I've done it to countless number of customer.
Of course, do it first in a test environment, ensure that it works, and TEST TEST TEST !!!
And only when you are feeling confident, then do it in the production.
Sincerely yours,
Alessandro
This thought was in the back of my mind also.
Am I correct in the assumption that my example above is what is need to expand the field?
Thanks
Quote from: rstockton on June 14, 2012, 08:14:35 PM
Am I correct in the assumption that my example above is what is need to expand the field?
Thanks
Hello,
yes you are right, that's exactly what you need to do.
Sincerely yours,
Alessandro
Thanks everyone
When I try the following command I get an error. Any ideas?
ALTER TABLE MKA1 ALTER COLUMN CustomerID SET DATA TYPE VARCHAR(24)
ksh: 0403-057 Syntax error: `(' is not expected.
Thanks,
Russell
Hello Russell,
am I wrong in the assumption that you wrote your command like that?
>ksh shell> $ db2 ALTER TABLE MKA1 ALTER COLUMN CustomerID SET DATA TYPE VARCHAR(24)
and not
<ksh shell> $ db2 "ALTER TABLE MKA1 ALTER COLUMN CustomerID SET DATA TYPE VARCHAR(24)"
???
If that's the case, you must be really carefull how you launch the commands, because the shell will try to interpret what you write before launching the command itself.
So to disable partly the interpretation of the shell, you need to put your command between double quotes, and disable completely then you need to have single quote.
In your case, the shell wanted to interpret the (
otherwise you might simply type
db2
and then when you are in the db2 command line, you can simply write your sql as you want.
Sincerely yours,
Alessandro
Thanks Alessandro, I had forgotten to add the quotes. I go it to work successfully, but I have a question about the following statement. Does this run as is or does it require quotes, and I am assuming this is not a DB2 statment?
update arsagfld set string_len=24 where agid=5817 and name='CustomerID'
Thanks,
Russell
After doing so experimentation I found my answer.
db2 "update archive.arsagfld set string_len=24 where agid=5937 and name='CustomerID' "