Tip:
Highlight text to annotate it
X
Hello and welcome to this Java Break with BASIS. Today we're going to talk about
automatic change auditing and replication for your BBX apps.
Our agenda
is going to first talk about "What is change auditing?"
We will have a look at the Audit Viewer and we'll tell you about how to access
change auditing and replication. And then we will talk about
an update we've done to the database and file replication...
tell you about some new wizards and some new capabilities.
It's going to be faster and easier.
And then we will finish off by briefly touching on
what you need to do to make this work with PRO/5
and Visual PRO/5 interpreters using the BASIS DBMS with
PRO/5 and Visual PRO/5. And then we will take your questions.
Let's get started. What is change auditing?
It's a feature that we've added to the product which
automatically logs changes to a selected list of data files.
So any BBx language file operation such as a WRITE or WRITE RECORD,
any ERASE or REMOVE, any SQL operations
such as UPDATE, INSERT, DELETE, CREATE TABLE,
DROP TABLE, ALTER TABLE all be logged in your
audit log. Now that audit database
is actually an ESQL database, other words
when you make these changes to your MKEYED files
either via SQL or via
READ RECORD, WRITE RECORD type structures those details are written to
the audit database
which is an SQL table. It will record things like the User,
the date and timestamp, the operation type and the record details depending
upon the type of operation.
For records that are written with an INSERT or a
WRITE RECORD or a WRITE statement, it will show you the record that is actually written
and give you the before and after record details.
For REMOVEs, we give you the key value on the REMOVE
and you could obviously then parse through the audit table to see what the
previous
values are of the fields.
We give you the ability to query that audit database by either using ad hoc
SQL, afterall, these are ESQL tables that'll contain
the record of the audit changes. Or
you can use the Audit Viewer that we have built in to
Enterprise Manager. Let's talk about the Audit Viewer.
You can view a list of the audit operations that match
a particular filter criteria - so date and timestamp,
User, or particular record or
file operations there were taken place. So, one or more users,
a particular file, one or more operation types, and a timestamp range.
So, we had the audit wizard let's see it in action. We are going to use the
Eclipse plugin
for the BASIS Enterprise Manager. And we will show you how we are going to
audit the database, make a change to the data,
and see what that change reveals
in the audit log.Lem me hand over to Jeff Ash who will take you through a detailed
explanation
and a demonstration of the new change
audit capability that we've added to the BASIS product set.
Let's take a look at how to create an audit job
and then also how we can interact with the
audit information once we have gathered that information.
So, let's take a look here at the audit logging item
under the filesystem node in the Enterprise Manager.
We pull this up and it shows us a list of our audit jobs and we click the 'Add'
button here
and it gives us a nice a wizard to walk through. So let's give it
a name and will call this one 'Chile Audit' and then we need to specify a
root directory. Now this root directory is a location where it's going to create
all of the audit databases. We chose to store the audit information
in an SQL database so that you can easily query that information from
reports,
from your own application, or from within the Enterprise Manager.
We give you a nice tool which we will look at in just a little bit for viewing that data
in a in a way where you don't have to specify any SQL or any complex query
information
but we give you that flexibility so that you can query the information
if you need to you in a very useful way using SQL.
So, we need to specify a location here for where those databases will get
created.
Now the database directory that we specify here
needs to be completely empty because the only thing that should be in this
directory
is audit databases. So for our example here, I'm going to put them in
/temp/
and Chile Audit.
Then all of my databases will be put in there.
Then I'm going to decide if I want to audit a named database instance
or manually specify the list of directories and files that I want to audit.
In this case, I want to go ahead and audit my entire database and so I'm going to just
select the Chile
database here. So, wehit 'Next' and now the next thing we need to do is select
the database that we want to audit and I want to audit the Chile Company
database.
We hit 'Next', then we need to determine how often we want the database to roll
over.
And what that means
when it rolls over it creates an entirely new
audit database that's completely empty. It leaves the old one intact, the
previous one intact,
but it creates a brand new one and that way gives you the ability to split out
your audit information
so that you don't have such a large audit database.
So, monthly we could set it to roll over. We can do it daily
or weekly or yearly. You can specify
the frequency or 'None' if you don't want it to ever roll over.
So we'll leave it at 'Monthly' and then the roll over frequency...
that would be one, would mean every month.
Two would mean every two months it would roll over and so forth.
Now we need to specify whether we're using advisory locking
and then we hit 'Next'. Now notice
as with creating a replication job, it gives us the list
of directories that it's going to be monitoring for our
auditing. We could
add additional directories here or additional files that we want to audit,
but in our case we won't add anything here. Then we
also have an exclusion page where we can exclude directories or files from our
audit job. In this case, we won't do that either...
so then we just hit 'Finish'
and now we're auditing the Chile company database.
So, we hit 'Refresh' there and that shows us all of our current information here.
Now we could pause this audit job if we don't want it to be auditing for...
for the time being and we have all those abilities
right here. So now let's take a look at
what the audit system puts together for us.
From here let's first take a look at the audit database
that's created. If we double-click on 'Databases'
we will find the Chile audit database here and you'll notice
it named the database the name of our audit job and
added on the date to the end of that name.
That indicates the date that that audit database was created.
Each time that it rolls over a new database would get created so you would
see
those additional data bases here. But what we can do is double-click on it
and open it up just like any other database and then we can
take a look at the tables that are available and we can
query these tables just like we would with any other SQL table...
but, we've given you a much easier way of looking at the audit data.
From here you could use SQL and build any complex query
and extract the information
that you're interested in and you could use that from a reporting tool
or from within the Enterprise Manager or some other SQL tool.
However, the easiest way is to go back to our audit logging jobs panel
and then we'll select our audit job
and we click on the magnifying glass.
What the magnifying glass does is that pulls up the audit database log
viewer
and this is where we can look at audit operations that are done
on our database.
Before we do that, we need to come over here and let's make a change to our
Chile Company database.
We need to do some update operations that
will cause the audit system to log some information.
What we've done here is we're going to execute an INSERT,
an UPDATE. and a DELETE so that you can see several different examples.
Now you'll notice here in the new SQL explorer in the new Enterprise Manager
you can actually execute multiple statements at one time
within one of the editors just simply by putting a semi-colon at the end
each SQL statement. We have three here...
and when we execute those then we get
some changes made. and now what we can do is come over to our
Audit log Viewer and
now we can filter the information. What we can do here is we can select
from the logdatabase that were interested in, in this particular case
there's only one available...
but each audit database will show up in that list as it gets created.
We can also select from the individual file that was modified if we're interested in audit
operations just on a particular file.
In this particular case, we have about three of our files in our database have
been modified and so those three modified files will show up in our list.
For our particular case here, we'll just look at all three so we'll leave that
blank.
You can also specify a listed users that you're interested in seeing operations
for
but we will go ahead and just include all the users and then you can also
specify the operation types.
If you're only interested in DELETE ESQL operations or DELETE record
operation you can put a check there
and then it will return only those
operations, those audit operations that match that criteria.
Again we'll just leave that blank and then we can specify
a Start and End Date and Time. Let's go ahead and just leave all the defaults
there and click
the magnifying glass and now that brings up our audit information.
Then what we can do here, as you can see each file that's been modified and the
type of operation. Let's take a look here
at some of these operations and see how these look
when we look at the additional detail that's available
on each one of those operations.
So let's take a look first at an INSERT RECORD operation.
Down here we have the Customer table,
the customer file that's been modified... we seen an audit operation and it tells us
the operation type is an INSERT RECORD type.
Let's double-click on that and it pops up a dialog
with information about that audit operation.
It shows us the raw record value that was inserted into that data file.
Now, if we wanted to, we could paste in a string template and I'll show you that
in our next example here
and that string template will tell us how to parse this
out it will break it down into a nice table layout down below
and let's all take a look at that now.
When we open up another operation this one is an UPDATE RECORD operation
and this happened on a different file,
on the customer file. So let's take a look now what we see here.
We see an old record value and then we also see a new record value...
and you can look here and see the raw records
and you can look here and see...
the name that was changed from 'McIntyre' to 'Ash'
and if we scroll through here we can see some other values that are
included here, but notice these numbers are kinda run together so it's hard to
tell
where the separation comes in.
What we can do is put in a string template here,
hit that, hit 'Refresh' and now what it's done is it's populated these nice
laid out tables for us and its parsed out
the information according to the string template that we specified.
Now we can scroll through and you can see here it separated out all our
numbers and now we can see that information a lot clearer.
This is very important if you're using a Type I
template column or if you're using a Type B
or a floating-point or a double.
So those types would show binary data that would not be
useful on its own and so by specifying the string template were able to see it
nicely parsed and formatted for us.
Now the final type is the DELETE RECORD type.
This type of operation will again pop up a nice viewer here for us
and it shows us the primary key value that was used to remove the record from
the data file.
and again, we could specify a string template if we want to if that
primary key value is made up of multiple columns
that might be useful for us. But on this particular dialogue for the DELETE
RECORD operation,
the string template we would specify would be one that would parse out
the primary key values only. So it wouldn't necessarily be the same as the entire
record.
And so as you can see with these examples here
the log file viewer gives you a very quick and easy way
to filter out information and return back
the audit operations that you are particularly interested in...
and then you can drill down further and see that detail.
But again, if you need further
querying capabilities or if you need to process that information
in a different way than what we provide here in this simple interface,
you're always welcome to go in and run complex
SQL queries against the audit database directly
and extract the information that you need to gather your auditing information.
Now let's recap on database file replication. We mention we have some new
wizards, so we'll take you through an example that but firstly 'What is it?'
It replicates specific files and/or directories. No code changes to your
application is required!
We can replicate to the same machine, to one or more more machines on your network.
we can replicate to one or more cloud machines and we can replicate an entire database
all completely transparent to the users.
So imagine here we are with your PRO/5 or BBj application server with the BBj
primary data source. We can replicate to another drive on the
same server or another space on the same drive.
We can replicate across local area network to another machine
and we can replicate across a wide area network. We can have multiple
replication jobs running simultaneously.
All direct changes to files are replicated.
WRITE and WRITE RECORD at a discrete level just as changes are replicated,
a REMOVE,
an ERASE, the creation of DIRECT, MKEYED, XKEYED, or VKEYED files
and all of the changes on those particular file types.
All SQL operations also replicated: INSERT, UPDATE, DELETE, CREATE/DROP TABLE and
INDEX, ALTER TABLE...
and the supported file types for the discrete record-by-record or only
changed...
replicating only the changes across to the replicated database,
offer in MKEYED, XKEYED, and VKEYED files, ESQL files, Serial files,
Direct, Sort and Program files.
String and other files are replicated by brute force copy.
So, what are the benefits?
Disaster recovery-obviously, a live database backup
locally, offsite and in multiple locations.
The system remains live, your production system remains live and users are uninterrupted.
You can create a 'read-only' copy of the database to offload queries and
reports to another server.
So there's no need to have those run on your production server, they can run on
the replicated server.
You can provide multiple databases in closer proximity to your user base.
Our new replication wizard is in the Eclipse Enterprise Manager plugin.
It's an intuitive user interface, quick and easy to use and monitor...
gives you a step-by-step guided wizard and you can also
replicate that functionality within BBj code or Java code by virtue of the Admin API.
So let's have a look at the new replication wizard and in fact we are
going to run this in a browser.
So let me hand over to Jeff.
Now let's take a look at how we create a replication job
in the Enterprise Manager. We are now using the browser version of the Enterprise
Manager
and let's come down to 'Replication Jobs'
under File System. We double-click that, it brings up a list
of all of our currently running replication jobs. Let's look at how we we create a
new job.
We click on the 'Add' button,
that brings up the replication Job Wizard...
and now what we can do is go through here it takes a step by step
through the process of creating a replication job. So the first thing to do
is to give our job a name,
the next thing we need to do is decide if we want to replicate a named
database instance
or replicate a list of one or more data files and directories.
If we replicate a named database instance then,
it will automatically pull in all of the data files
and data dictionary files that we need
to replicate our entire database. If we choose to replicate a list of one
or more data files,
then we need to manually specify that information ourself.
In this example, just to keep it simple, we will go ahead and use the named
database instance.
In either case, you can specify additional files or
exclude files once you get to that portion of the wizard.
So, in either case you have the same power and flexibility
just using the named database instance takes the initial legwork out...
required to add to list of data files and directories that make up your database.
So we select our database that we want to replicate and we want to do the Chile
Company database.
Notice it also asks us if we want to create a database on the target
and what that means is that wherever we replicate to,
the machine that we replicate to, it will create a new database name for us
and in this particular case its chosen use ChileCompany_Replicated.
That database name will point to our replicated data files and so if we
choose to
perform queries on that target...
those target files, then we can do that and it has automatically configured
it for us.
In this case, we will go ahead and do that just to keep things simple,
but if you choose not to, then your files will still be there
but there won't be any database reference to those data files.
We want to specify the Target Admin Server Port for creating
the database on the target because that is how it will connect
to the target machine to create that database.
We leave those defaults in place, we hit 'Next'.
The next thing we need to do is specify the target
and in this case I'm going to just replicate to my local machine just to
keep it simple.
So I specify local host and now giving the port
number for the file system server that is running on the target
and then a valid username and password that will be used
to create those files and manage those files on the target.
If my target system is using SSL on the file system server,
I would need to put a check mark here, otherwise I'd leave that unchecked.
Once you have your information in, it's a good idea to click the 'Test
Settings' button
so that you can verify that what you entered is correct.
In this particular case, it is, so we're good, so we hit 'Next'
and then we can specify a maximum bandwidth if we don't want a replication
job to use up
as much network bandwidth we can limit that in some way if we want to.
We'll just leave that at the default and then we need to specify the correct
advisory locking setting
and then we continue on...
and now you can see it's added all of the data files...
well the directories that
make up our database. So it added our dictionary, our data
and then it also added the config directory because we have
a reference to configure BBX from some stored procedures.
So, we don't need to add anything else here but we could. Now we could exclude
files if there are files that we didn't want to exclude for many of these
directories
we could do so on this page...
but we don't need to here so all that is left to do is to click 'Finish'.
We hit 'Finish' and our replication job is created
and it's running and it tells us the last time it was in sync
which means it's all caught up now. Now if we wanted to pause this we could
select this and we could hit the 'Pause' button
or we can do the 'Pause when caught up' option as well. All that can be done
right here from the interface.
Additionally, you can do everything I just showed you
using the Admin API, programmatically from a BBj
or Java-based application.
Thanks Jeff. So we can also edit a replication job.
We have limited editing of job information. We can also edit
bandwidth usage, whether it's synchronous or asynchronous,
which files to include, which directories to include and which ones to exclude.
Let me read a comment from Frank Anderson from Computer Outfitters.
"I've now had it stable for over six months and for less than three hundred
dollars a month
I have a near real-time (typically less than two minutes behind) offsite backup
to Amazon of 20 directories
with over 3,000 files on a system with about 300 active users.
My primary data center is in San Francisco, so there's a real risk of going offline
at some point.
I've been working through the disaster recovery testing, and it looks like if I
lost my main system,
I could have all the remote users back up and running on Amazon within an hour.
I didn't make ANY changes to my application to get it setup and working."
So really a great reference. Some more detail though on Computer Outfitters'
cloud replication - they are spending about three hundred dollars a month.
Could probably be cut in half by using Linux instead of Windows and of course you can
have Linux
on your replicated server and still have Windows on your production server.
Their database size is 70 gigabytes, they are actually paying for 400 gigabytes
because they also have daily cloud backups that push up their usage
and cost in the Amazon cloud. So, your mileage will vary.
You may be able to get away with just a few cents a day
to be able to replicate your data to a cloud machine.
How to get to change auditing and replication.
Firstly, this is just a reminder. There is no data conversion required!
So even if you running the PRO/5 or Visual PRO/5 database right now, to move
across to the BASIS database under BBj's control,
requires absolutely no data conversion.
If you're on Visual PRO/5, all you need to do switch from the PRO/5 Data Server to
the BBj
PRO/5 Data server. And if you are on PRO/5,
you need to install BBj and implement the Data Server syntax.
If you're on BBj, of course, there's nothing for you to do. Just a reminder
imagine this is you running
in Visual PRO/5 and BBj. All you have to do
again, is to change your data server to BBj
and there are some database configuration changes. There is no 'config.bbx'
for the data server. Instead you use the Enterprise Manager to configure
the BASIS DBMS, the PRO/5 data server functionality
and you use server-side PREFIX configuration in the Enterprise Manager.
What that means is that the PREFIX will be searched on the server, instead of going
back and forth between the client and server. There is better functionality, improved
capability. We recommend you use FCBCASHE whenever possible.
It keeps the files open. Of course you can't use that if you are using the same name
for multiple files and you are relying on the PREFIX change to find it
in different directories. We also suggest that to use the 'mode=persist'
on file OPENs. What that does is keeps the network connection between the
client to the server open.
Some performance considerations. This is a
fairly useful chart... the BBj/PRO/5 Data Server
we've used here a 64-bit MKEYED file with 600,000 records
with between 256 and 2,048 byte
record sizes and just run through a fairly standard preliminary
WRITE, sequential READ and DIRECT READS. A very simple test,
but you'll see the performance is comparable
between Visual PRO/5 to PRO/5 Data Server and in fact in a couple of cases,
its better performance. And of course if you want really
lightning fast performance, move some your batch jobs into BBj, use the XCALL
functionality
and you'll get more than 10-fold performance improvement
in those batch jobs when running with the BBj Database.
Interactive programs.. you won't see the gain
and performance improvements. There is no consideration necessary.
Any READ/WRITE intensive apps, report programs,
they could cause a network round trip so we would strongly suggest
making these more efficient using XCALL functionality to run the program in BBj
on the same machine as the data. That give you a huge performance gain over
Visual PRO/5.
Batch programs... again consider utilizing BBj to run those directly
on the server.
And of course, some of the other gains you'll get is a scalability
increase and a resource decrease because the BASIS DBMS runs on a single process in
the JVM
with multiple threads for the multiple connections from the clients.
Whereas, the PRO/5 Data Server runs a new process on each connection
to the database. So that's especially important for resource constrained
Windows servers. High user count deployments will benefit the most
from a move to the BBj Database. If you're in the PRO/5 and BBj world...
so if you want to run the BBj Database with PRO/5, this may be what your
configuration looks like in a character-based environment.
What do you need to do? Well, you need to add the data server syntax in you config file and/or your code
when you are opening
a file. So add the host
and port ID prefix to the directory name.
We again suggest you use servier side PREFIX configuration in Enterprise Manager
that will search it on the server.
And we also suggest using FCPCACHE where possible,
using the 'mode=persist' on file OPENs keeping that network connection open.
So here are some of the performance considerations. If you compare
PRO/5 again using that default 64-bit MKEYED file
doing some very simple WRITEs, sequential READs, and direct READs...
you will see again performance is very comparable
in that environment and
you should not expect to see any change in your user's
experience with interactive programs. There is no consideration necessary.
For READ/WRITE intensive apps, we suggest you consider using XCALL to BBj.
and similarly in your batch programs. If you are running PRO/5 and Visual PRO/5,
and not using the BASIS database management system
here are the benefits by making the change. You can now start using
change auditing and replication - No code changes to your application.
VKEYED and XKEYED file types can now be accessible to your app.
So variable length record or multiple key segments and unlimited key segments are
available.
Triggers and stored procedures. You have access to BBj for integration tasks
to third party products for utilities, for web services, for scheduling,
and of course, you have access to the browser user interface.
So, a ton of value by merely switching across to use the BASIS database
management system
with your PRO/5 and Visual PRO/5 interpreters.
Some replication resources if you want to learn more have a look at some of our
prior Java Breaks-
"Enabling Data Replication and Auditing for PRO/5, Visual PRO/5 and BBj",
"Database and File Replication" and "Backing Up Your BBx Data to the Cloud
in a Heartbeat" will give you more information about how to do that with the
BASIS supplied AMI, Amazon machine instance.
In summary today, we've covered what change auditing
is all about. We talked about the Audit Viewer,
how to access change auditing and replication...
we've spoken about the new wizard with database and file replication,
the update. It's been faster and easier...
and we've shown you how you can start moving across if you are still running PRO/5 and
Visual PRO/5,
to taking advantage of the BASIS DBMS functionality under BBj.