If you’re a kid in Canada and you’ve just GOT to watch The Big Comfy Couch … you need to know what time it’s on! Go to the YTV Web site where real-time scheduling information is available at your fingertips. YTV was able to convert several time-consuming manual processes for gathering information for its TV Guide and Web site schedules by accessing directly its AS/400 data with the StarSQL ODBC driver. In this Cinderella story, YTV was able to ensure consistency of information, free up and/or reallocate 10+ man hours per week, saving more than $10,000 annually.

Seven and a half million of the 10 million television households throughout Canada watch YTV, whose slogan is “Keep it Weird”. With such top-rated programs as Pokemon, Buffy the Vampire Slayer, The Addams Family, ReBoot and Nanalan’, YTV may be even bigger in Canada than Nickelodeon is in the United States. In fact, YTV and Nick have collaborated on several shows including the wildly popular Animorphs.

In television, a key business system is the Traffic system, which plans TV schedules and reconciles everything that goes to air. That's every program—every segment within each program, every commercial break and every commercial contained in the break. Any voiceovers, the little network logo bugs in the corner and those things in TV lingo called credit squeeze zooms, (at the end of a program, it squeezes over the credits, so it can show you another promotion) are also tracked. It's basically a list or a log of all of those items and how and when they go to air. It's a huge mission critical system and even encompasses advertisers and billings. When the sales department sells advertising space, it's booked into the same system, accounted for and historically tracked in the traffic system.

That traffic system is a software application and at YTV, it’s the sole application running on their AS/400. Their AS/400 is dedicated to that function because it is such a large, important application and requires a lot of processing power.

The functioning of this system is entrusted to Steve Rolufs, YTV’s Director of Information Technology and New Media. YTV’s AS/400 was basically a standalone system. “For years, we used printouts of our traffic log that went to master control, and as schedule information to TV Guide, both the national TV Guides and also regional and newspaper-based television guides, and Satellite guides as well, across Canada,” said Rolufs. There are dozens of different publications that need YTV's listings. “For several years after that, we worked with a process that still required several manual steps: 1. Go into the traffic system on the AS/400, 2. Generate the log output file, 3. Identify the output file, 4. Download it through Rumba (a PC to AS/400 connectivity program) onto the PC, 5. Import it into Word document and 6. Format it, which took quite some time because it was fairly ugly-looking text had to be manually worked over, and then finally, 7. The information was sent out to the various TV Guide publications on diskette.” Rolufs continues, “We were never completely happy with that as a solution.”

With the grace and analytical problem-solving process we see from Tommy Pickles in every episode of Rugrats, Rolufs set out to discover new adventures in data access.

YTV is also a big Microsoft BackOffice shop. Beyond the AS/400 traffic system, everything is pretty much LAN-based , NT servers, SQL Server databases and they use BackOffice. “One day, we got an upgrade to our SNA Server from Microsoft,” Rolufs states. “The upgrade included a single license version of the StarSQL product from StarQuest Software. I thought, ‘Wow! This could really open up a lot of doors for us by letting us go in and directly query our AS/400 data.’ But, at the time, it didn't really click yet what I wanted to use it for. But I thought, this is pretty cool and I played around with it a little bit.”

Later on, when Rolufs started thinking about this more, he realized there was a business need here. In fact, he realized a few business needs that could be met by this product. Rolufs concluded, “If we could reach directly into the AS/400 and pull out schedule information down to the PC and automatically format those TV listings, we could cut out several manual processes.” The new process of directly accessing AS/400 data actually started with the YTV Web site. Two years ago, YTV launched its Web site, Error! Bookmark not defined., running Microsoft Internet Information Server (IIS) using Active Server pages (ASP), Dynamic HTML and SQL Server. Rolufs discussed his ideas for direct AS/400 access with Miljan Braticevic, YTV’s Website Programmer. Braticevic says, “One of the features that we added onto the Web site was schedule information so that kids and families across Canada could go to our Web site and see what was playing, and when, throughout the week. But, again, what we were working from downloaded reports and all the information from the schedule was being re-keyed by someone on our Web site team and created newly for the Web.”

Rolufs wondered, “How could we save some of the time of this individual who was re-keying this information and having to deal with last-minute scheduling changes?” So, the logic process continues and Rolufs asks, “We've got this schedule information. We've got a traffic department that's continually updating this information anyway and making these changes. So, can we just go right to source and just put this information in directly?”

That’s how the conversation started. At the same time, they also knew that the preparation of TV listings for the TV guides was also a bit of a clutch and required many manual steps. Rolufs explains, “Shazam! We saw the light and we came up with one integrated solution that could reach right into the AS/400 DB2 database. Through the StarSQL ODBC driver, we could produce the TV listings, populate a SQL database right on our Web server that could then be presented to the users on our Web site and they could drill down and we could just look at that information any which way. It just was a bit of an epiphany to come up with this solution.”

Rolufs credits Braticevic as a genius when it comes to taking his vision and translating it into a real solution. Rolufs says, “He's the one that made this happen, using the ODBC driver, Microsoft’s ADO and SQL Server and another important aspect of it, COM, Microsoft's Component Object Model.” Braticevic explains, “We just went to the Web site for StarQuest, www.starquest.com. We found out we could upgrade StarSQL to get more features and we got additional licenses. What StarSQL did for me was enable direct access to our database on the AS/400. I'd say the most difficult part was understanding the database schema and writing the SQL queries. But as far as StarSQL goes, everything went perfectly. I did get some help from tech support in the beginning when it came to installing the driver. Then, I was able to populate our SQL Server database with just a few manual steps.”

The StarSQL driver is used with an application program to bring the data down from the AS/400 and populate the SQL Server database on the Web server. Only the information needed to present to TV Guide or for the Web pages showing the schedule is required. On the AS/400, there are literally hundreds of fields with pieces of information that are not needed at the Web server and are discarded during the replication process. Now, someone might ask, “Why doesn't the Web server itself just go out and get the database information in real time?” Braticevic explains, “The answer is that it would be too many layers and we couldn’t guarantee the performance. By refreshing the local SQL Server copy on the Web server, it enables us to support our traffic and generate the Active Server pages that we need.” Rolufs continues, “We get somewhere around four million hits per week, so we must have a very high performance solution. That's why we architected it the way we did.”

The solution has been in production since September '98. “We’re moving tens of thousands of records—several months of scheduling information grabbed at a time with details of name of the show, what time it's playing, description of the show, the episode title, etc.,” said Braticevic. “StarSQL just worked perfectly for us and we gained the performance we needed.” Rolufs adds, “It also allowed us to take a business process that had been absorbing manpower in our Web site department and move it back to the functional group where it belongs—where the TV listings were actually being produced anyway. So, we killed two birds with one stone. We made them more efficient in a process that they were already involved in and it also eliminated an entire process in another functional group.” For Rolufs, who manages the IT group as well as the Web group, it was a way to free up a resource to work on other things and enhance the YTV Web site.

Will YTV be able to take the components of this solution and apply it to other areas for future growth? “We currently have another process in place where we report on “Canadian content” to a regulatory body called the CRTC, the Canadian Radio-Television and Telecommunications Commission.” The CRTC can be compared to the FCC in the US, alike in some ways, but different in others. An important part of YTV’s business is ensuring that it meet certain regulations and guidelines as far as being a Canadian broadcaster in broadcasting Canadian content—shows that are either produced in Canada or are at least partner productions with Canadian companies.

In order to ensure that YTV is following conditions of their broadcast license and to get certain tax breaks on productions, etc., they have to submit Canadian content reports to the CRTC on a monthly basis. Currently, someone manually prepares this information. Here again, it's very much schedule-based information. “It's a log, like the TV Guide listings, or the schedule guide on the Web site—traffic logs. There are fields already in the traffic database for these things, but again, we're at the point in the process where a person is executing a report on the AS/400, doing a manual download and then formatting it manually.” Rolufs says, “And it hit us after we came up with the solution for the TV Guide listings and the Web site listings that there was a very similar process going on. We can re-use a lot of the code, use the same technology and use the StarSQL ODBC driver and come up with a solution for these people as well. In this situation, someone preparing these reports can just hit a button, it reaches across into the AS/400, pulls out the Canadian content information and a copy of the schedule, formats it to the requirements of the CRTC and readies it for the monthly report that we have to submit electronically.” Rolufs has identified this as another area that could benefit from a direct AS/400 access solution very much like the scheduling solution for the YTV Web site.

“It really was a painless solution to put in place. Working with StarSQL was a pleasant surprise.” Concludes Rolufs, “The technology was sound and the support was there and it worked the way we expected it to. And those are the types of products and technologies that we like.”