IBM CPLEX MSSQL Database Connection
Eğer bu yazıyı okuyorsanız CPLEX’in ne olduğu hakkında bir fikriniz vardır. Ama eğer bilmiyorsanız, Wikipedia ve IBM sitelerindeki açıklamaları inceleyebilirsiniz.
CPLEX bir optimizasyon çözüm yazılımı. CPLEX’i çok uzun süredir severek kullansam da, ne yazık ki dünya çapında çok yaygın bir kullanımı olmamasından dolayı ekosistemi çok gelişmemiş durumda. Kendi dokumantasyonu ve örnekleri dışında bir yardıma ihtiyacınız olduğunda ya da bir hata ile karşılaştığınızda zor anlar yaşayabiliyorsunuz. Neyseki, Alex Fleischer ve Eray Çakıcı gibi CPLEX’e gönül vermiş kişiler var da, sorunlarımızı kısa sürede çözebiliyoruz.
CPLEX Aralık 2020'de yeni bir sürüm çıkardı. Hatta versiyon sistemini değiştirdiği için, 12.10'dan bir anda 20.1'e geçiş yaptı. Eski versiyonlama sisteminde, büyük değişiklikler major versiyonu, geliştirmeler ve iyileştirmeler için de minor versiyonu artıyordu. Alınan karar doğrultusunda, yeni versiyonun çıktığı sene major ve o sene içindeki geliştirme ve iyileştirmeler de minor arttırma olarak yapılmasına karar verildi.
Versiyondur değişir. Peki bunun konumuzla ne alakası var? CPLEX versiyon atladıkça, çözücünün gücünü arttırmaya devam ediyor. Süreler kısalıyor, çözümler iyileşiyor. Fakat, bizim gibi CPLEX’i bir ürünün içine konumlandıran kişiler için önemli bir handikap ortaya çıkıyor. Veritabanı bağlantı metotlarının değişmesi. 12.5 ve öncesinde kullanılan veritabanı bağlanma metotu, 12.6 sürümünde kullanılamamaya başlanmıştı. Bu çok büyük bir problem olmuştu. 12.6'da kullanılan metot güvenlik açısından daha iyi olduğu iddia edilse de, fonksiyonel olarak sıkıntıları da beraberinde getirmişti. 20.1 sürümü ile bu sıkıntılar giderildi ve artık çok daha kolay bir şekilde veritabanı bağlantı yapılabilmektedir. (12.6'da kullanılan metot 20.1'de hala kullanılıyor fakat yenisini öğrenip uygulamanızı tavsiye ediyorum)
Bu yazımda, 12.5 ve öncesi, 12.6 ve sonrası, 20.1 için MSSQL veritabanı bağlantılarını nasıl yapacağınızı anlatacağım. Benim gibi internette kaynak arayıp bulamayanlara gelsin :)
Authentication
SQL Server’a bağlanmanın 2 temel yöntemi vardır. Bunlar:
1- Windows Authentication
2- SQL Server Authentication
Bu yazımızda SQL Server Authentication anlatacağız. Eğer OPL Studio üzerinde yazdığınız bir modeli, zamanlanmış bir görev ile çalıştırmayı hedefliyorsanız ya da bir uygulamaya bağlayacaksanız tavsiyem SQL Server Authentication. Nedeni de, Windows Authentication ile bağlantı kurmuşsanız, CPLEX kurulu sunucunun restart olması durumunda ilgili kullanıcı ile o makinaya login olmadığınızdan dolayı o model çalışmayacaktır. Ya da sizin kullanıcız logout olduysa da aynı problemle karşılaşırsınız. Çalıştığınız şirkette SQL Server Authentication için yetki almanız zor olsa da sadece ilgili SQL sunucuya ve ilgili tablolara yetki isteyerek bunu çözebilirsiniz.
Örnek Veritabanı
Aşağıda bağlantı yollarını (connection string) paylaşacağım kodlar için CPLEX’in içinde gelen Oil örneğindeki veri setlerini kullanacağım. SQL’de input (girdi) tablosu ve bir tane de örnek çıktıları yazacağım boş bir tablo oluşturuyorum. Denemek isterseniz diye bu tabloları oluşturduğum scriptleri de yazıyorum.
--Input tablosu
CREATE TABLE dbo.OilData(
capacity int NULL,
price float NULL,
octane float NULL,
lead float NULL
)INSERT INTO dbo.OilData (capacity, price, octane, lead)
SELECT 5000, 45, 12, 0.5INSERT INTO dbo.OilData (capacity, price, octane, lead)
SELECT 5000, 35, 6, 2INSERT INTO dbo.OilData (capacity, price, octane, lead)
SELECT 5000, 25, 8, 3--Output Tablosu
CREATE TABLE dbo.OilResult(
capacity int NULL,
price float NULL,
octane float NULL,
lead float NULL,
result float NULL
)
.mod dosyası
OPL tarafında bir model oluşturduğunuzda 1 zorunlu 2 opsiyonel dosya oluşturursunuz. Zorunlu olan mod (model) dosyasıdır. dat (data) ve ops (settings) ise opsiyoneldir. Eğer modeliniz, bir veritabanı, excel ya da başka bir veri kaynağına bağlı ise dat dosyası da zorunlu hale gelmiş olur.
Aşağıda her bir sürüm için dat dosyası içinde nasıl MSSQL veritabanına bağlayacağımızı yazacağız. Bunun öncesinde mod dosyasında, bağlayacağımız tuple nasıl olacak onu da yazalım.
tuple oilData {
int capacity;
float price;
float octane;
float lead;
}
{oilData} OilDatas = …;Veritabanından okuduğumuz tabloyu bu tuple içine atacağız. Sonrasında modelimiz çalışacak ve sonucu da tekrar veritabanında bir tabloya yazmak istiyoruz. Çıktı tablomuzu da oluşturalım
tuple oilResult {
int capacity;
float price;
float octane;
float lead;
}
{oilResult} OilResults = …;12.5 ve öncesi
Veritabanı bağlantısı açmak için .dat dosyamıza şu cümleyi yazıyoruz
DBConnection db(“oledb”,”kullaniciAdi/sifre/database/server”);“db” artık bizim bağlantı anahtarımız. Bunu kullanarak, okuma ve yazma işlemleri yapacağız. Hatta MSSQL’de bulunan stored procedureleri bile çalıştırabiliriz.
DBRead → OkumaDBUpdate → Yazma ve Execute etme
Önce okuyalım ve input tuple dolduralım.
OilDatas from DBRead(db,”select capacity, price, octane, lead from dbo.OilData”);Artık input tablomuz doldu. Sonrasında modelimiz çalıştı ve sonuçlar OilResults tupleında bulunuyor. Bunu MSSQL’de bulunan dbo.OilResult tablosuna yazmak için de aşağıdaki kodları kullanıyoruz.
OilResults to DBUpdate(db,”INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);Son hali nasıl oldu ona da bakalım:
DBConnection db(“oledb”,”kullaniciAdi/sifre/database/server”);OilDatas from DBRead(db,”select capacity, price, octane, lead from dbo.OilData”);OilResults to DBUpdate(db,”INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);
12.6 sonrası
12.5 ve öncesindeyken herşey bu kadar kolaydı. Fakat 12.6 ile birlikte DBConnection kullanılmamaya başlandı. Bunun yerine JDBC üzerinden veritabanı bağlanma zorunluluğu getirildi. Bunun için öncelikle deveye hendek atlatmanız gerekiyordu. Hendek de şu, Java driverlarını indirmeniz gerekiyor ve bu driverları modelinizin bulunduğu klasörde tutmanız gerekiyordu.
Aklınızın karıştığını görüyorum. Bu sebeple daha detaylı anlatacağım.
Öncelikle IBM tarafından hazırlanan GitHub Repository’e bakmanızı öneririm. Tüm gerekli dosyaları buraya yüklemişler. Sadece açıklamaları yetersiz yapmışlar.
İlk olarak bilgisayarınızda Java’nın (Java SE Development Kit) kurulu olduğundan emin olmalısınız. Kurulu değilse şu likten indirip kurulumu gerçekleştiriyorsunuz. Kurduktan sonra command pencersinde şunu yazıp test edebilirsiniz.
java -versionİkinci aşamada, java.js ve jdbc-custom-data-source.jar dosyalarını indirmeniz gerekiyor. IBM github reposunda bunlara da yer vermiş. Şu linkten bunları da indiriyorsunuz.
Son olarak, MSSQL için gerekli olan JDBC Driverını indirmek. Microsoft’un sitesinden de indirmeniz için gerekli linki buraya bırakıyorum.
Evet, Java SDK kurulu ve gerekli olan dosyalar elimizde. Bu gerekli olan dosyaları, CPLEX projemizin bulunduğu klasöre koyuyoruz. Yani .oplproject, .project, dat ve opl dosyalarınızın bulunduğu klasöre.
Gerekli dosyaları son kez kontrol edelim:
jdbc.js
jdbc-custom-data-source.jar
mssql-jdbc-7.4.1.jre8.jarŞimdi .dat dosyasını yazmaya başlayabiliriz. (Hendekten atlamak üzereyiz. Çok mutluyuz)
ODBC bağlantısındna farklı olarak, JDBC üzerinden bağlantıyı prepare{} içinde yazmamız gerekiyor. Aşağıda adım adım yazıyorum, son halini en altta bulabilirsiniz.
prepare {
includeScript(“jdbc.js”);
var db = JDBCConnector(“jdbc:sqlserver://server;databaseName=database;user=kullaniciadi;password=sifre”);
}Bağlantımızı oluşturduk. Süslü parantezi kapatmadan okuyacağımız tabloyu yazıyoruz.
db.read(“OilDatas”, “select capacity, price, octane, lead from dbo.OilData”);Ardından, modelden çıkan sonucu tabloya yazıyoruz.
db.update(“OilResults”, “INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);İşte oldu. Son halini de paylaşalım.
prepare {
includeScript(“jdbc.js”);
var db = JDBCConnector(“jdbc:sqlserver://server;databaseName=database;user=kullaniciadi;password=sifre”);db.read(“OilDatas”, “select capacity, price, octane, lead from dbo.OilData”);db.update(“OilResults”, “INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);}
Sadece connection string olarak mı problem var? Neden bu kadar deve ve hendek dedin bize diyebilirsin. Aslında sadece bağlantı için yazdıklarımız değil. Asıl problem, model içinden sql tarafında yapmak istediklerinizi istediğiniz sırayla yapmanıza engel oluyor. Mesela önce oku, sonra model sonuçlarını yaz en sonda da gidip sql tarafındaki bir tabloda bir flagi update etmeniz gerekiyorsa gidip ilk başta update edebiliyor. prepare içine çok müdahale edemiyorsunuz.
Ben bunu nasıl çözdüm. Flow ile çözdüm. Başka bir yazıda daha detaylı anlatırım. Fakat yaptığım, Ana bir mod dosyası üzerinden, alt (sub) mod ve dat dosyalarını çalıştırarak ilerledim. Bu sayede, WarmStart’ı da OPL üzerinden başarılı bir şekilde uygulamış oldum.
20.1 ve sonrası
Günümüze geldik :) IBM 12.6 ve sonrası yaşanan sıkıntılardan çok başı ağrımış olacak ki, 20.1 ile birlikte ODBC bağlantı öelliğini geri getirmiş. Biz de açıkçası bu durumdan çok memnun olduk. Fakat, baktığımızda konu hakkında bilgi veren içerik olmadığını gördük ve üzüldük.
İlk olarak şunu söyleyelim. IBM CPLEX versiyonlarını geliştirmesinin temel amacı, çözücünün performansının arttırmaktır. Arkada çalışan modellerin, sezgisel algoritmaların, parametrelerin ve bilgisayar gücünün verimli kullanılmasının geliştirilmesi ile yeni versiyonları ortaya çıkmaktadır.
Aşağıda CPLEX sürümlerinin model çözüm sürelerinin karşılaştırılmasını görebilirsiniz.
12.5 sürümünü kullanan ve MSSQL bağlantısı ile modellemiş bir kullanıcı, lisans yenileme yaptıktan sonra 12.6'da modelinin çalışmadığını görmek büyük bir probleme sebep olmuştu. ODBC bağlantılarını JDBC’ye çevirmek, veri okuma performansları ile başetmek üzmüştü.
Neyseki, 12.6 ve sonrasında kullanılan JDBC connectorları 20.1 sürümünde de kullanabiliyorsunuz. Buna ek olarak, 12.5'te bulunan ODBC bağlantıları tekrar geri geldi. Daha doğrusu, CPLEX’in yeni sürümünde bu driver yüklü olarak geldiği için, herhangi ek bir driver kurulumuna ihtiyaç olmadan çalıştırabiliyorsunuz. Sadece syntax biraz değişti. Gelin isterseniz buna bakalım.
Öncelikle bizim de bu bağlantıları yaparken faydalandığımız linki paylaşalım. Yine IBM’in kendi sitesinde CPLEX dokumantasyonunda da gün geçtikçe bilgiler artıyor. (Gözler yaşlı :( )
Veritabanı bağlantısı için ilk cümlemizi yazıyoruz.
ODBCConnection db(“Driver={SQL Server};Server=server;Database=database;Uid=kullaniciadi;Pwd=sifre;”, “”);Bağlantı cümlemizin sonunda bulunan “” içine her çalışan sorgunun öncesinde çalışmasını istediğiniz bir sorgu varsa bunu yazabiliyorsunuz. Örneğin, her yazma öncesinde bir sp exec etmek isterseniz onu yazabilirsiniz. Bunu ben boş bırakıyorum.
Tuple için okuma cümlesine geçiyoruz.
OilDatas from ODBCRead(db, “select capacity, price, octane, lead from dbo.OilData”);Gördüğünüz üzere, ODBCRead komutunu okumak için kullanıyoruz. Yazmak için de aşağıdaki cümleyi kullanıyoruz.
OilResults to ODBCPublish(db, “INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);Tupleda bulunan verileri de ODBCPublish ile tekrar veritabanına yazmış oluyoruz.
Burada bahsetmem gereken bir konu daha var. 20.1 sürümünde, iki farklı connection açabiliyoruz. (Önceki sürümlerde bu özellik var mı bilmiyorum) Yayınlalan örneklerde, okumak için bir connection, yazmak için bir connection açılmış. Bu sayede, yazmak için açılan connection özelleştirilerek yazılan tablonun yazılmadan önce boşaltılması sağlanabiliyor.
.dat dosyasının son halini yazalım.
ODBCConnection db(“Driver={SQL Server};Server=server;Database=database;Uid=kullaniciadi;Pwd=sifre;”, “”);OilDatas from ODBCRead(db, “select capacity, price, octane, lead from dbo.OilData”);OilResults to ODBCPublish(db, “INSERT INTO dbo.OilResult(capacity, price, octane, lead, result) VALUES(?,?,?,?,?)”);
Güncel Problemler
13.04.2021 tarihi itibari ile 20.1 sürümünü çok yoğun bir şekilde henüz kullanmadığım için testlerimi tamamlamadım. Fakat şu ana kadar yaşadığım sıkıntıları siz yaşamayın diye burada yazıyorum. Kullandıkça yazının bu kısmını güncelleyeceğim. Buradan takip edebilirsiniz.
Karakter uyumsuzlukları
CPLEX 20.1 sürümü veri tipi seçiyor. Veritabanı katmanındaki tablolarınızdaki bazı veri tiplerini CPLEX’deki tuplelara alamıyorsunuz. nvarchar ve decimal veri tiplerini CPLEX kabul etmiyor. Bu sebeple, ya tablodaki formatlarınızı değiştirmeniz gerekiyor ya da okurken CAST() yaparak nvarchar → varchar ve decimal() →float dönüşümlerini yapmanız gerekiyor. Konu ile ilgili IBM forumundaki başlığa şu linkten ulaşabilirisiniz.
Performans Çekinceleri
Önceki sürümlerde olduğu gibi, bu sürümde de veritabanından okuduğunuz tabloların kolon ve satır sayısına göre timeout alma ihtimaliniz ne yazık ki bulunuyor. Yaptığım testlerde kolon sayısı 30'u geçen bir tablonun satır sayısı 1000'i geçtiğinde timeout alıyordu. Bu gibi durumların yaşanmaması için sadece ihtiyacımız olan kolonların modele alınması faydalı olacaktır.
Ek Bilgi
IBM CPLEX için hazırlanmış dokumanlarda çok kısa bahsedilen ama benim çıkış noktam olan, bir ayrıntıya da yer vermek istiyorum.
20.1 sürümünün örnek klasöründe bulunan ve yeni bağlantı özelliklerini tanıtan dosyalar çok faydalı. Çoğu veritabanı için bağlantı cümlecikleri için birer örneğe yer verilmiş. Başlamadan önce bir göz atmanızı tavsiye ederim.
C:\Program Files\IBM\ILOG\CPLEX_Studio201\opl\examples\opl\tabledataSon Sözler
Bir yazımızın daha sonuna gelirken CPLEX’in tüm sürümlerinde MSSQL bağlantısını incelemiş bulunuyoruz. Umarım, CPLEX kullanan kişiler için faydalı bir yazı olmuştur.
CPLEX’i OPL üzerinden değil de Python kütüphanesi DOCPLEX ile kullanmayı düşünüyorsanız Eray Çakıcı’nın GitHub hesabına göz atabilirsiniz.
Doğrusal programlama hakkında yakın zamanda çok güzel bir yazı yayınlayan Orkun Berk Yüzbaşıoğlu’nun blogunu da okuyabilirsiniz.
Son olarak, HBR’de hem sektör hem de optimizasyon hakkında yazılar yazan Şükrü İmre’yi de takip etmenizi öneririm.
Herkese iyi modellemeler :)
