We see that many questions about how to retrieve a list of the Producers and Consumers and associated protocols like FTP, SFTP and about routing channels assigned. For the above question all that information is not located in the same table and might require joining few tables.
This articles guides to get above info and please find below some database table information that can help to develop the requires queries.
A) The first place to check would be the table YFS_ORGANIZATION
B) The best way to get a list of producers and consumers is using the SCI_PROFILE table, this contains all the partners setup in SI, not only SFG but all other partners like AS2 for example.
The SFG partners have on the OBJECT_NAME a _CONSUMER or _PRODUCER suffix after the partner name, for example Partner1_CONSUMER, so need to develop an SQL query that selects from SCI_PROFILE table all entries that contain CONSUMER or PRODUCER in the OBJECT_NAME.
If the SFG partner is a producer and a consumer at the same time, then it will be listed twice on that table with a different suffix.
But the best way to organize your partners and have a quick overview would be to create a group in SFG called Consumers and another one called Producers, then assign all consumer partners to the group consumers and all producer partners to the group producers; this way by viewing one group or the other, you will be able to view all partners belonging to that group.
Lists all channels configured in SFG, you can obtain the producer of each channel and the consumer.
The consumer will only be specified if the channel uses a static template, if the template is dynamic, then the consumer will not be specified since it will only be determined during routing.
Can be used to obtain a list of all SFG partners, since all SFG partners are members of a group called "All Partners", an SQL query like below would retrieve a listing of all partners:
select * from FG_PART_GRP_MEMB where PART_GRP_KEY='All Partners'
3. Using the ORGANIZATION_KEY from point 2, find the user account for that partner by querying the database table YFS_USER
4. Also using the ORGANIZATION_KEY from point 2, find the the ORGANIZATION_NAME (Partner name) and the OBJECT_ID by querying database table YFS_ORGANIZATION
5. Using the OBJECT_ID from point 4 that equals the ENTITY_ID of table SCI_ENTITY_EXTNS, obtain all the configuration of the partner
using the columns EXTENSION_KEY and EXTENSION_VALUE
There will be one entry for each partner parameter that you configure in the UI.
The Extension keys that should be consider are the following:
There are other keys that might also indicate the protocol used.
6. For the protocol usage, you can also use the Object_ID from point 4 that matches the ENTITY_ID and query the table YFS_TRANSPORT to find out the protocol details.
For database information in general, under the installation directory of SI, find an explanation of all database tables used by SI here.
Please note that development of SQL queries falls outside of support scope and customer should need to contact IBM services via your IBM account manager if they need help to develop them.