IDUG is dedicated to the support of not only database administrators, but also developers who use our databases. This means that we’re dedicated to sharing information about the many features of SQL and of DB2 in particular. Why is this important? Well, for some very basic reasons.
- Delivering SQL-based solutions can happen quickly
Developers can generally construct and test SQL quickly. Therefore, if the SQL can incorporate significant programming features then the resulting application processing can also be developed and tested quickly.
- SQL is portable
SQL runs on the server to which it is directed. Therefore, if more application logic can be pushed into an SQL statement, then that statement can be placed into a variety of application programs or embedded in various tools, on a variety of platforms. This gives an enterprise leverage in selecting platforms.
- Advanced SQL can be a performance advantage
This performance advantage is realized when a process that requires multiple database calls can be consolidated into a single database call. Reducing the calls to the database, the network message traffic, and quantity of data sent by the database and processed by the application can result in a dramatic performance improvement of a data intensive process.
SQL is a Programming Language
Have read you the SQL manuals lately? There is one for DB2 for z/OS and two for DB2 for LUW. Many people use these reference as just that, references. However, I frequently encourage people to actually read the manuals. In the very least you should read the “SQL Queries” chapters (chapter 6 in the DB2 for LUW SQL Reference Volume 1, chapter 4 in the DB2 for z/OS SQL Reference). It is absolutely amazing what you can do with the SQL language in DB2:
- Subqueries, joins, anti-joins
- Complex built-in functions and user-defined functions
- Online analytical functionality
- Recursion, looping
- XML processing and generation
- JSON processing
- SQL PL programming for stored procedures, functions, and triggers
Getting a good SQL education can be a bit of a challenge. There are some good classes out there and plenty of examples to be found on the internet. However, some of the best education possible is to just give it a try. This is actually really easy for developers to do as DB2 Express-C and IBM Data Studio can both be downloaded for free from the IBM web site, and the DB2 sample database can be created for immediate testing of various SQL capabilities.
The Dojo Concept
The concept of the SQL Dojo was brought to IDUG and the IDUG Content Committee by Content Committee member Kurt Struyf. Kurt had attended a local application developer’s user group meeting and was blown away by the comradery and support brought out by the Dojo exercise. As IDUG was coming up with more ideas to support application developers, Kurt had the brilliant idea to bring the dojo concept to SQL and IDUG. It was an instant success and we’re expanding on it every year.
The concept of a dojo is quite simple. A group of developers (and/or DBAs and Sysprogs) gather together and are individually and randomly assigned to teams. Then a single computer is used on a single display with a portable keyboard and mouse to present a series of programming challenges. Teams are chosen randomly and each team is given just a few minutes, to solve the programming challenge. If the team solves the programming challenge, they move on to the next one; if they don’t solve the challenge before their time runs out, the same challenge (including previous teams’ unfinished work) moves on to the next randomly selected team. Once a team has been selected their number immediately goes back into the pool, and so they could be selected many times during the dojo and must stay engaged. Now comes the real fun; not only does a participant get to meet and work with other people in their group, but participants are also welcome to shout suggestions to the team working on the challenge in an effort to help them out. This can result in a very active and funny discussion with a timer running out, multiple people shouting out suggestions, and the team racing to implement the solution and beat the clock. The result is a fun and cooperative event where people with a common interest meet others, network, work cooperatively, and learn something about application development. It’s great fun and a great learning opportunity.
Kurt brought the SQL Dojo concept to the IDUG North America Technical Conference in 2013. We called it “Fun with SQL” and it was an instant hit! We’ve been expanding on it ever since.
April 2015 “Fun with SQL” Challenge
This month on the Content Committee blog we’ll be doing an online version of the “Fun with SQL” dojo. Each week we’ll present an SQL challenge and request the community to solve it. People can respond to the challenge in the comments section of the blog post, or they can email Kurt directly at email@example.com with a solution or idea and he will post it to the blog. You can post a possible answer, ask a question, and comment on something someone else has posted, or offer alternatives to something that’s been posted. Don’t be shy about posting your answer if someone else has already posted. At the end of the month, the content committee will take the names of the people that have participated and randomly select one to receive a reward of an IPad case, courtesy of IBM, and one to receive a $50 Amazon voucher! So, don’t be shy, just dive in and share your experience or inexperience and get a free and fun education!
Here is the first SQL challenging based upon the DB2 sample database:
Construct a query that gives in one line per employee the employee’s first name, last name, department name, salary, as well as the average and total salary of the department where the employee works.
Here is an ERD representing the DB2 sample database:
“Fun with SQL”, the SQL Dojo at IDUG Technical Conferences
If you are planning on attending an IDUG Technical Conference (or are considering attending), then consider the “Fun with SQL” dojo at the upcoming IDUG Technical Conferences. We will be having the dojo at IDUG North America, IDUG EMEA, and IDUG Austalasia! As I have mentioned before, it is geared toward developers, but everyone is welcome, and any skill level is welcome! We’ll have subject matter experts on had to guide you in the experience and you’ll have some laughs while meeting likeminded people and learning something about SQL. We’ll also be introducing “Fun with SQL” to the IDUG booth with new interactive challenges, led by IDUG hall of fame speaker David Simpson and the IDUG Content Committee Team. Another way to meet people, have a good time, and learn something!
Are you an application developer, or know application developers that have avoided IDUG conferences because they are DBA oriented? Well, that has changed! Not only do we have the SQL Dojo but we’ve increased the developer focused content dramatically. Check it out, attend, and have yourself some “Fun with SQL”!!!