As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
Hello: Does anyone know how to use the BOT tables to report off them to show what distro list is associated with what schedules. Unfortunately and sadly IBI cannot and do not know how to provide an answer. For an IT solutions company, I am appalled that they do not understand their own table structures and don't even have an ER diagram of their own tables. And to think that when an IB consultant goes out on an engagement, one of the key requirements from IBI is to request an ER diagram. I hope there is someone out there who like us, have the same need for reporting off the BOT tables.This message has been edited. Last edited by: FP Mod Chuck,
I looked at the database diagram in SQL Server and it doesn't appear to be a way to JOIN the 4 tables together. As Evan said you can join the BOTSCHED to BOTLIST with the SCHEDULEID and you can JOIN BOTADDR to BOTDEST with the ADDRBOOKID but there is no connection between the two groupings. DISTLIST is not populated when you use a Distribution list in the schedule it is only populated with individual e-mail addresses when that option is used.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
Posts: 2128 | Location: Customer Support | Registered: April 12, 2005
Chuck/Evan: That is exactly our findings too. But in order to connect a schedule to a email address we need to connect the 4. At least that is my understanding. It is as if there is another table missing but I cant find it and IBI as no help. They said they knew nothing about hoe the tables connected and that I would need to purchase more software if I wanted to get information about how these tables connect. I am just stunned
Or am I missing something in my understanding of the tables?
Do you and Evan have ant reports that go against these tables? All our existing reports in WF77 no longer function because of the table changes.
Here, IF you use SQL Passthru, BUT, if you're really on 7.1.3, this may not work. We're using DB2. Tables changed when we upgraded to 8.2. This searches for an email string, just remove the filters: USER table is in our datamart, remove...
-SET &EMAIL_STR = 'CPSFUELTSM' ;
ENGINE DB2 SET DEFAULT_CONNECTION &DBNAME
WHEN 'admin' THEN 'FCO'
ELSE U.USER_FSTNM||' '||U.USER_LSTNM
END AS OWNER
,BSIT.INTERVALTYPE AS FREQUENCY
,BT.TASKOBJ AS EXECUTABLE
WHEN 'L' THEN 'LIBRARY'
WHEN 'M' THEN 'EMAIL'
END AS DELIVERY_METHOD
,BDIST.DISTLIST AS DIST_LIST
,BD.DESTFN AS DIST_DETAIL
FROM VFCR.BOTSCHED BS
JOIN VFCR.BOTTASK BT
ON BS.PACKETID = BT.PACKETID
LEFT OUTER JOIN VFCR.BOTADDR BA
ON BS.IBFSID = BA.IBFSID
LEFT OUTER JOIN VFCR.BOTDEST BD
ON BA.ADDRBOOKID = BD.ADDRBOOKID
JOIN VFCR.BOTDIST BDIST
ON BS.SCHEDULEID = BDIST.SCHEDULEID
JOIN VFCR.BOTSIT BSIT
ON BS.SCHEDULEID = BSIT.SCHEDULEID
JOIN VFCR.USER U
ON BS.CASTER_USER = U.USER_LOG_ID
( UCASE(BDIST.DISTLIST) LIKE '%&EMAIL_STR.EVAL%'
UCASE(BD.DESTFN) LIKE '%&EMAIL_STR.EVAL%')
AND BS.ACTIVE = 'Y'
FOR FETCH ONLY WITH UR;
TABLE ON TABLE HOLD AS EMAILSRCH FORMAT ALPHA
TABLE FILE EMAILSRCH
Our repository is in SQL Server, and we use this query to get each job and the email addresses that it is distributed to:
SELECT schd.JOBDESC 'rc_job'
, schd.CASTER_USER 'schd_userId'
, addr.BOOKNAME 'dist_list'
, dest.FLDVLU 'burst_value'
, IIF(addr.BOOKNAME IS NULL,dist.DISTLIST,dest.DESTFN) 'email_address(s)'
FROM dbo.BOTSCHED AS schd WITH(READUNCOMMITTED)
LEFT JOIN dbo.BOTDIST AS dist WITH(READUNCOMMITTED) ON schd.SCHEDULEID=dist.SCHEDULEID
LEFT JOIN dbo.BOTDEST AS dest WITH(READUNCOMMITTED) ON dist.DISTLIST=dest.ADDRBOOKID
LEFT JOIN dbo.BOTADDR AS addr WITH(READUNCOMMITTED) ON addr.ADDRBOOKID=dest.ADDRBOOKID
ORDER BY 1
But to echo Tom, our repository is probably different than yours
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015