Hi,

I would like to realize the function that exporting the data, for example, tiitm001 to a text file per minute. As I know, JOB can
do it, but I don't know how to set it.

Environment: BaaN IV4C, UNIX, Oracle

Thanks!

Steven

Steven,

Could you expand this question, are you talking about a function called JOB in Baan or Oracle? And what are you trying to achieve?

Steve

The function I want to achieve includes:

Export several tables in BaaN system to txt files

The txt files will be saved under centain directory on the UNIX server

The export will be done automatically and repeated every minute. That means the data in txt format will be updated by BaaN table within one minute. Anyway, the speed depends on the size of the table.

The export will be done within BaaN lever not Oracle level. Therefore, we don't want to use Oracle tools to do the transfer.

When job daemon is running, the job can be run automatically. While I don't know how to write the scipt and how to let the job in BaaN untility export the data repeatly.

That's all.

Thanks!

Steven

Hi Steven,

If I am getting this right what you require is this:

You will need to set the job up in company 000, that means setting up a user with this company as their default.
Have this user start the jobdaemon from their cron each morning and stop it each night or as required.
Make a job in company 000 with ttaad5100m000 Maintain Job Data,
set the periodical field
have the period set to x minutes
and the status set to In Queue
In ttaad4226m000 Creat Sequential Dump of Tables
set the company, the package and the table,
set the field with the seperator required etc,
then from Tools drop menu make it a job and add this to the job you just made.

Hopefully this will be what you need.


Steve

I'm not sure why you would want to export the data into a textfile every minute but I would not suggest running session ttaad4226m000 Create Sequential Dump of Tables to accomplish this. This session locks the table and your users may experience slowdown and/or errors when invoking sessions that access the table.

I suggest finding another way to do this like creating a customized session that scans the table and writing to a textfile. This session should ignore realtime updates while scanning. Is there a Oracle utility to do this? Just an idea because I dont know what you're trying to accomplish.

Hi Steven,

As Benito suggested, you might consider to make yourself a tool which does not lock the table while processing.

You can utilise the option - of using the prepared set clause in your SQL for optimization.


Prepared set.
With this option, the entire set is retrieved before the first record is returned. The set is temporarily stored. This option is useful when a process simultaneously selects and maintains (or deletes or adds) records. In this case, changes must not be visible in the selected records. The prepared set option forces a consistent read. The syntax is as follows:
SELECT ... [from][where] AS PREPARED SET


Also refer to another option for your SQL -

http://www.baanboard.com/programmers_manual_baanerp_help_functions_db_operations_set_transaction_readonly

I wonder why do you need a a process to be executed every minute this will make the system busy on a large volume of records.

I set the job to the above advices and let it in queue. Unfortunately, it does not work. Maybe there is something wrong with the job daemon. Please tell me some more advices how to set the job daemon.

Steven

Hi there,

just a hint : if ttaad4226m000 (Create Sequential Dump of Tables) locks the table and you don't like to create an own session you can use an Exchange-Scheme export data.

I'm no prof about exchange, but it seems to be possible to use it in a job. Does anyone think about this way ?

PB

Hi Steven,

Benito is correct, session ttaad4226m000 Create Sequential Dump of Tables does lock the table, a fact I forgot to consider sorry. I have had another thought, have you asked your Oracle DBA if he can write a trigger for the table, again something I have not tried but it might do the trick. This way every time a record is added or updated a copy could be made in a seperate table that Oracle could write out to a txt file for you.


Steve

I would agree with PB that using Exchange for this task and then exporting the data may be the best option.

With regards to checking the operation of the job daemon you may be better off testing it with another job you know definately works.

You could also check the application locks for signs of the job daemon running.

Also take a look at your Baan startup script rc.start it should contain something like script shown below.

We run ours by having a user job*** for each company in which we run jobs ie. job001, job002 and then the user job*** is the owner of all jobs in that company but I think some of this was covered in Steve's earlier post.

#Optional to start the jobdaemon.
USER=job000;export USER
TERM=vt100; export TERM
${BSE}/etc/rc.startjobdm
sleep 10
USER=job001;export USER
TERM=vt100; export TERM
${BSE}/etc/rc.startjobdm
sleep 10


Regards
Neal Matthews
Intier Automotive - IT Support Analyst

I have to agree with Steven, you can not use Seq Dump, with the lock it creates you will not have a very good chance of getting the table. Your best bet is to have your dba dump the table on a the timed basis that you need. From this point you can use a script to move the txt file to where you need it.

The question in my mind is why you need this dumped every minute?

What I really want is to create B/S application to get data from BaaN system.

I created a web site based on Microsoft IIS and installed a Sql_Server database. The user may use Internet Explorer to have access to the Sql_server to get the data expecially the history data. The interface may be developed by VB or VC.

The left problem is how to synchronize the data between Sql_Server and Oracle Server on which BaaN is installed. To avoid the table locking problem, I hope to export the data from BaaN system to txt file and import txt file to Sql_Server. Therefore, I need the export action per minute. Is there any other better solution?

Tks!

Steven

If you do Exchange, you may consider Exchange base on Audit. That would export only changes, not the full tables. You would have to start Auditing on the tables used. This also has an overhead price.

For IIS, you can use Microsoft's ASP (Active Server Pages) to query the Oracle table directly. You can then use Internet Explorer. This is what I would do personally. The syntax is,


ODBC Driver for Oracle (from Oracle)
oConn.Open "Driver={Oracle ODBC Driver};" & _
"Dbq=myDBName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Where: The DBQ name must be defined in the tnsnames.ora file.

Or, by using an Oracle trigger (as somebody mentioned above) to replicate to another table. Perhaps a combination of Oracle trigger, SQL DTS utility. I'm just throwing ideas here.

In regards to Baan job, look at doc number AG1001, Job Management, Solution number 1518 available at the Baan Support website.