How do people dump current sequence values from a runtime license of the database?
According to the KB it is impossible to do this from the data dictionary. Do you have to use custom code? Or connect remotely from a development license?
Here is the kb:
The Data Admin API is basically doing the same under the covers, but requires less code for the implementer. The main benefit is that it is meant to work also if the database implementation is changed.
The code would look something like this. (I might have overlooked some functionality in the example) There should probably be a catch and error handling.
Export:
---------------------------------------------
block-level on error undo, throw. using OpenEdge.DataAdmin.DataAdminService from propath. using OpenEdge.DataAdmin.IDataAdminService from propath. using OpenEdge.DataAdmin.ISequence from propath. using OpenEdge.DataAdmin.ISequenceSet from propath. using OpenEdge.DataAdmin.Lang.Collections.IIterator from propath. define variable Service as IDataAdminService no-undo. define variable oSequenceSet as ISequenceSet no-undo. define variable oSequence as ISequence no-undo. define variable oIter as IIterator no-undo. define stream seq. output stream seq to "seqvals.d". Service = new DataAdminService("mydb"). oSequenceSet = Service:GetSequences(). oIter = oSequenceSet:Iterator(). do while oIter:hasNext(): oSequence = cast(oIter:Next(),ISequence). export stream seq oSequence:name oSequence:CurrentValue. end.
delete object Service. // is not garbage collected
-------------
Import - Edit: corrected loop to use repeat
------------------
block-level on error undo, throw. using OpenEdge.DataAdmin.IDataAdminService from propath. using OpenEdge.DataAdmin.DataAdminService from propath. using OpenEdge.DataAdmin.ISequence from propath. using OpenEdge.DataAdmin.ISequenceSet from propath. using OpenEdge.DataAdmin.Lang.Collections.IIterator from propath. define variable Service as IDataAdminService no-undo. define variable oSequenceSet as ISequenceSet no-undo. define variable oSequence as ISequence no-undo. define variable oIter as IIterator no-undo. define variable cName as character no-undo. define variable iSeq as int64 no-undo. define stream seq. input stream seq from "seqvals.d". Service = new DataAdminService("mydb"). oSequenceSet = Service:GetSequences(). oIter = oSequenceSet:Iterator(). repeat: import stream seq cName iSeq. oSequence = oSequenceSet:Find(cName). oSequence:CurrentValue = iSeq. end. Service:UpdateSequences(oSequenceSet).
delete object Service.
Thanks Peter,
I'm curious what Progress DBA's would do for sequences when they need to perform dump and load operations. It seems there should be some prepackaged tool for this, right?
I suppose they can just connect to the database remotely from a development license ... but that makes me nervous and I think that type of an approach should normally be discouraged, right?
and if you do have a dev license on your production machine, make sure it is locked down (main stream users/application can't benefit) or you might get a tap on the shoulder ............
If I have a modern release of Progress I use a program that leverages the dynamic sequence stuff that Peter referred to.
If not? As I recall (it has been a while since anything that ancient reared its ugly head) I think I recall that -rx and some xcoded routines get the job done.
Or you you can write a custom procedure for the target db, compile it in dev and then run the r-code. It's not very complicated.
OK, I will pass this along to our DBA. Thanks for all the feedback.
The Data Admin API supports read and update of sequence current-value.
The example below shows both read and update of the current value. The ISequence also allows read and update of the other sequence properties.
Note that the code below does not work for Multi-tenant databases. This is supported through the ISequence:SequenceValues property that returns a map of tenant names and values.
------------------------------
block-level on error undo, throw. using OpenEdge.DataAdmin.IDataAdminService from propath. using OpenEdge.DataAdmin.DataAdminService from propath. using OpenEdge.DataAdmin.ISequence from propath. define variable Service as IDataAdminService no-undo. define variable oSequence as ISequence no-undo. define variable iSeq as int64 no-undo. Service = new DataAdminService("mydatabase"). oSequence = Service:GetSequence("mysequence"). iSeq = oSequence:CurrentValue. oSequence:CurrentValue = iSeq + 1. Service:UpdateSequence(osequence).
Continued:
BIG DISCLAIMER: I do not know if this works on a runtime license.
The Data Admin API is OO code. So any OE release that it works on will also have the dynamic-current-value function and statement. Which means that generic r-code will work just fine without any need to compile on the fly.
Have you seen this article? knowledgebase.progress.com/.../P170593
I was looking for a sequence dump/load solution recently and came across it. It uses the DYNAMIC–CURRENT–VALUE function mentioned above. It works out of the box, but I made some custom modifications so that I could call it from another procedure that sets the current database (DICTDB alias) and iterates over a set of databases.
I was not familiar with the Data Admin API though, so going that route might be a better solution.
The Data Admin API is basically doing the same under the covers, but requires less code for the implementer. The main benefit is that it is meant to work also if the database implementation is changed.
The code would look something like this. (I might have overlooked some functionality in the example) There should probably be a catch and error handling.
Export:
---------------------------------------------
block-level on error undo, throw. using OpenEdge.DataAdmin.DataAdminService from propath. using OpenEdge.DataAdmin.IDataAdminService from propath. using OpenEdge.DataAdmin.ISequence from propath. using OpenEdge.DataAdmin.ISequenceSet from propath. using OpenEdge.DataAdmin.Lang.Collections.IIterator from propath. define variable Service as IDataAdminService no-undo. define variable oSequenceSet as ISequenceSet no-undo. define variable oSequence as ISequence no-undo. define variable oIter as IIterator no-undo. define stream seq. output stream seq to "seqvals.d". Service = new DataAdminService("mydb"). oSequenceSet = Service:GetSequences(). oIter = oSequenceSet:Iterator(). do while oIter:hasNext(): oSequence = cast(oIter:Next(),ISequence). export stream seq oSequence:name oSequence:CurrentValue. end.
delete object Service. // is not garbage collected
-------------
Import - Edit: corrected loop to use repeat
------------------
block-level on error undo, throw. using OpenEdge.DataAdmin.IDataAdminService from propath. using OpenEdge.DataAdmin.DataAdminService from propath. using OpenEdge.DataAdmin.ISequence from propath. using OpenEdge.DataAdmin.ISequenceSet from propath. using OpenEdge.DataAdmin.Lang.Collections.IIterator from propath. define variable Service as IDataAdminService no-undo. define variable oSequenceSet as ISequenceSet no-undo. define variable oSequence as ISequence no-undo. define variable oIter as IIterator no-undo. define variable cName as character no-undo. define variable iSeq as int64 no-undo. define stream seq. input stream seq from "seqvals.d". Service = new DataAdminService("mydb"). oSequenceSet = Service:GetSequences(). oIter = oSequenceSet:Iterator(). repeat: import stream seq cName iSeq. oSequence = oSequenceSet:Find(cName). oSequence:CurrentValue = iSeq. end. Service:UpdateSequences(oSequenceSet).
delete object Service.
The iterator can be removed from the import example...
OOPS: It is important to replace the do while true with repeat.
repeat: import stream seq cName iSeq. oSequence = oSequenceSet:Find(cName). oSequence:CurrentValue = iSeq. end.