generic flow service

, ,
1

Hello,

We are trying to push updated data from 80 tables in batch mode on daily basis. There is no transformations between data for approx 40-50 tables. Rest of them has lots of transformations. Of course the data size is minimal

The whole process is SELECT from source db, MAP to target document and INSERT into target db.

I am wondering if I could write a generic flow service for the above 40-50 tables. Is there any way to achive this?

Please advice.

Thanks,
Sateesh

2

Here’s one approach that you could use for all of them:

  • Maintain a list of select, transformation and insert statements somewhere. The insert and select could be complete are partial SQL statements. A file of the format:
3

Thanks for your quick response.

I think I got your idea but I am still somewhat unclear. If possible, could you please elaborate more specifially on how only one dynamic SELECT, INSERT can be coded for multiple tables, which have different column names since Dynamic SQL requires Output fileds to be specified at design time.

Appreciate your statement on PROs and CONs

4

Sateesh,

I think what Rob is saying is that your actual SQL will be kept as a string in the list, along with the name of the service that you will use with that select to transform the selected data and the SQL string for inserting this transformed data into the target table. For example you can create an XML file that looks smth like this:
[highlight=xml]

<?xml version="1.0"?> SELECT X, Y, Z FROM TAB WHERE C='VAL' FullyQualifiedNameOfService INSERT INTO TAR(R,T,Y) VALUES('VAL1','VAL2','VAL3') SELECT X, Y, Z FROM TAB-A WHERE C='VAL' FullyQualifiedNameOfService INSERT INTO TAR-A(R,T,Y) VALUES('VAL1','VAL2','VAL3') [/highlight]

Now have your generic service read this xml into a documentList, loop over the document list and in each iteration,

  1. Use the stmtSelect string in a Dynamic Q template to select the data,
  2. Use the servTransform string to invoke the service that does the transformation and
  3. Use the stmtInsert string in another Dynamic Q Template to insert the data into the target table.

PS: Remember, the transform service can be blank (in cases where you don’t need the transformation at all) - in such cases you should skip the invoking transformation service.

HTH, Rohit

5

Ah, that’s right. The output for the dynamic and custom templates need to be defined or you end up with nothing in the pipeline. This might be a good time to use WmDB which would allow the services to be more general at design time, I think. You’d just end up with “results” and your transform and insert steps would make assumptions about what fields are present there.

6

Thanks Rob and Rohit.

You are right Rob, Dynamic SQL query is not completely Dynamic (specially when it comes to retrieve the data)

Is it good to use WmDb for database queries keeping webMethods support in mind on future product releases?

Probably I should drop the idea of coding generic service.

7

wM has previously tried to deprecate WmDB. I think the backlash if they ever really did drop it would be huge.

Another approach, if you want a single service that manages this sort of DB replication, is to list select and insert service names instead of SQL/SQL fragments. You’d create your 50-80 select and insert services and then list those in your control list.

Yet another approach is to not use IS for the replications that don’t have any transformation–use DB tools (or ETL tools) which are much better suited for DB data replication.

8

Ah, I see… my bad I didn’t even read Sateesh’s full post to see that he pointed out to each select returning different columns. You guys are on the mark.